AutoHotkey Community

Posted: **26 May 2017, 07:47**

Hello,

Here are two excel files A.xls & B .xls in the C:\ . When I open the B.xls then press F6, data will copy to file B from A.xls .
If I don't want open the file B, how can do it?
Thank you.

Code: Select all

F6::
xl := ComObjActive("Excel.application")
wb0 := xl.ActiveWorkbook
lRow := xl.ActiveSheet.Range("A65536").End(-4162).Row
wb1 := xl.Workbooks.Open("C:\A.xls")
wb1.ActiveSheet.Range("A1:D100").copy(wb0.ActiveSheet.Cells(lRow+2, 1))
wb1.Close()
ObjRelease(xl)
return

Posted: **26 May 2017, 07:53**

Try this and let me know:

Code: Select all

F6::
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\B.xlsx")
lRow := wb0.ActiveSheet.Range("A65536").End(-4162).Row
wb1 := xl.Workbooks.Open("C:\A.xlsx")
wb1.ActiveSheet.Range("A1:D100").copy(wb0.ActiveSheet.Cells(lRow+2, 1))
wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
xl := ""
return

Edit: removed the ObjRelease that was in the original code as it was pointed out it wasn't needed.

Posted: **26 May 2017, 08:39**

Nightwolf85 wrote:Try this and let me know:

Code: Select all

F6::
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\B.xlsx")
lRow := wb0.ActiveSheet.Range("A65536").End(-4162).Row
wb1 := xl.Workbooks.Open("C:\A.xlsx")
wb1.ActiveSheet.Range("A1:D100").copy(wb0.ActiveSheet.Cells(lRow+2, 1))
wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
ObjRelease(xl)
return

GOD !!!! THANK YOU !!!

Posted: **26 May 2017, 08:55**

Nightwolf85 wrote:
Code: Select all
ObjRelease(xl)

ObjRelease is not for this. At most, all that is required is freeing the variable: xl := ""
But even that isn't required since you did xl.quit. There is no object anymore after this.

ObjRelease(Ptr)
Ptr - An unmanaged object pointer or COM interface pointer.
- https://autohotkey.com/docs/commands/ObjAddRef.htm

Posted: **26 May 2017, 09:05**

Guest wrote:ObjRelease is not for this

I just took the original code provided and added the part requested, I didn't look into anything else and it wasn't stopping the code from working, but thank you for letting him know.

Posted: **26 May 2017, 09:38**

Guest wrote:...But even that isn't required since you did xl.quit. There is no object anymore after this.

Really ?
You have a ghost after that.
Press CtrlShiftEsc and check you still have Excel.

So, it is good to use this style.

Code: Select all

XL.Quit
XL =

Posted: **26 May 2017, 19:24**

All,
Thanks.

Posted: **27 May 2017, 00:04**

IMEime wrote:So, it is good to use this style.
Code: Select all
XL.Quit
XL =

TIL, thanks.

Posted: **27 May 2017, 07:03**

All,

Sorry, I don't understand, I type some number in the column1 and column2, why can not be saved in file A and file B ?
Thanks.

Code: Select all

Gui +AlwaysOnTop
Gui, Color, dcdcdc
Gui, Font, S09, Tahoma
Gui, Show, x131 y91 h120 w180,AAA
Gui, Add, Text, x5 y15, A
Gui, Add, Edit, vColumn1 x20 y10 ; w75 h25
Gui, Add, Text, x5 y45, B
Gui, Add, Edit, vColumn2 x20 y45 ; w75 h25

F6::
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\B.xls")
lRow := wb0.ActiveSheet.Range("A65536").End(-4162).Row
wb1 := xl.Workbooks.Open("C:\A.xls")
wb1.ActiveSheet.Range("B1").Value := %Column1%
wb1.ActiveSheet.Range("C1").Value := %Column2%
wb1.Save()
wb1.ActiveSheet.Range("A1:D100").copy(wb0.ActiveSheet.Cells(lRow+2, 1))
wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
xl := ""
return
GuiClose:
ExitApp

Posted: **27 May 2017, 12:11**

Add this to the top of your F6 hotkey:
Gui, Submit, NoHide

This is an expression, so remove the %%.
wb1.ActiveSheet.Range("B1").Value := %Column1%

Posted: **27 May 2017, 17:04**

kon wrote:Add this to the top of your F6 hotkey:
Gui, Submit, NoHide

This is an expression, so remove the %%.
wb1.ActiveSheet.Range("B1").Value := %Column1%

Thank you.

Posted: **07 Jun 2017, 08:05**

Hello,

When I press the Copy button then open B.xlsx, I can get something like attach file(1.jpg). In fact, A6: F13 = A5: E5.
The last number of the F Row is how many items in the A.xlsx.
If I want the same as the attach file(2.jpg) A6: F13 = A5: E5. how can I do ? @@"

THANK YOU!

Code: Select all

#NoTrayIcon
Gui +AlwaysOnTop
Gui, Color, 111111
Gui, Font, S10 EEAA99, Tahoma
Gui, Show, x131 y91 h200 w218,Copy
Gui, Add, Text, x5 y15 cFFCC00, A_C
Gui, Font, S14 EEAA99, Tahoma
Gui, Add, Edit, x43 y10 w170 h25 vCard1
Gui, Font, S10 EEAA99, Tahoma
Gui, Add, Text, x5 y48 cFFCC00, B_C
Gui, Add, Edit, x43 y44 w170 h25 vCard2
Gui, Add, Text, x5 y81 cFFCC00, C_C
Gui, Add, DropDownList, x43 y78 w70 h200 vCard3, A|B|C
Gui, Add, Text, x120 y81 cFFCC00, D_C
Gui, Add, DropDownList, x155 y78 w58 h200 vCard4, 01|02|03|04|05|06|07|08|09|10
Gui, Add, Text, x5 y114 cFFCC00, E_C
Gui, Add, DropDownList, x43 y112 w70 h200 vCard5, S1|T1|Q1
Gui, Add, Button, x132 y112 w80 h24 cFFCC00,Copy
Send {Tab}

#Persistent
WinGet, a_ID, ID, A
SetTimer, aa, 500
return

aa:
WinGet, a1_ID, ID, A
If ( a_ID != a1_ID )
k_ID = %a1_ID%
Return

FormatTime, TimeString
MsgBox The current time and date (time first) is %TimeString%

ButtonCopy:
Gui, Submit, NoHide
WinActivate, ahk_id %k_ID%
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\B.xlsx")
lRow := wb0.ActiveSheet.Range("F1048576").End(-4162).Row
wb1 := xl.Workbooks.Open("C:\A.csv")
wb1.ActiveSheet.Range("A1:D100").copy(wb0.ActiveSheet.Cells(lRow+2, 6))
wb0.ActiveSheet.Cells(lRow+3, 1).value := "Date"
wb0.ActiveSheet.Cells(lRow+3, 2).value := "Group1"
wb0.ActiveSheet.Cells(lRow+3, 3).value := "Group2"
wb0.ActiveSheet.Cells(lRow+3, 4).value := "Group3"
wb0.ActiveSheet.Cells(lRow+3, 5).value := "Group4"
FormatTime, TimeString,,M/d/yy HH:mm
wb0.ActiveSheet.Cells(lRow+4, 1).value := TimeString
wb0.ActiveSheet.Cells(lRow+4, 2).value := card5
wb0.ActiveSheet.Cells(lRow+4, 3).value := card3 card4
wb0.ActiveSheet.Cells(lRow+4, 4).value := card2
wb0.ActiveSheet.Cells(lRow+4, 5).value := card1
wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
xl := ""
return
GuiClose:
ExitApp

Posted: **09 Jun 2017, 08:03**

Try this and let me know.

Code: Select all

#NoTrayIcon
Gui +AlwaysOnTop
Gui, Color, 111111
Gui, Font, S10 EEAA99, Tahoma
Gui, Show, x131 y91 h200 w218,Copy
Gui, Add, Text, x5 y15 cFFCC00, A_C
Gui, Font, S14 EEAA99, Tahoma
Gui, Add, Edit, x43 y10 w170 h25 vCard1
Gui, Font, S10 EEAA99, Tahoma
Gui, Add, Text, x5 y48 cFFCC00, B_C
Gui, Add, Edit, x43 y44 w170 h25 vCard2
Gui, Add, Text, x5 y81 cFFCC00, C_C
Gui, Add, DropDownList, x43 y78 w70 h200 vCard3, A|B|C
Gui, Add, Text, x120 y81 cFFCC00, D_C
Gui, Add, DropDownList, x155 y78 w58 h200 vCard4, 01|02|03|04|05|06|07|08|09|10
Gui, Add, Text, x5 y114 cFFCC00, E_C
Gui, Add, DropDownList, x43 y112 w70 h200 vCard5, S1|T1|Q1
Gui, Add, Button, x132 y112 w80 h24 cFFCC00,Copy
Send {Tab}

#Persistent
WinGet, a_ID, ID, A
SetTimer, aa, 500
return

aa:
WinGet, a1_ID, ID, A
If ( a_ID != a1_ID )
k_ID = %a1_ID%
Return

 ; Note these lines will never run, so you don't need them.
FormatTime, TimeString
MsgBox The current time and date (time first) is %TimeString%

ButtonCopy:
Gui, Submit, NoHide
WinActivate, ahk_id %k_ID%
xl := ComObjCreate("Excel.application")
wb0 := xl.Workbooks.Open("C:\B.xlsx")
wb1 := xl.Workbooks.Open("C:\A.csv")
lRow := wb0.ActiveSheet.Range("F1048576").End(-4162).Row
aRow := wb1.ActiveSheet.Range("A1048576").End(-4162).Row ; The count of rows in A.csv, if column A can be empty then choose one that won't be...
wb1.ActiveSheet.Range("A1:D" . aRow).copy(wb0.ActiveSheet.Cells(lRow+3, 6)) ; This change will cover cases where there are more than 100 rows in A.
wb0.ActiveSheet.Cells(lRow+3, 1).value := "Date"
wb0.ActiveSheet.Cells(lRow+3, 2).value := "Group1"
wb0.ActiveSheet.Cells(lRow+3, 3).value := "Group2"
wb0.ActiveSheet.Cells(lRow+3, 4).value := "Group3"
wb0.ActiveSheet.Cells(lRow+3, 5).value := "Group4"
FormatTime, TimeString,,M/d/yy HH:mm
Loop % aRow -1 ; Loop the number of rows from A.csv -1 for headers, if there are no headers remove the the `-1`
{
	offset := A_Index + 3
	wb0.ActiveSheet.Cells(lRow+offset, 1).value := TimeString
	wb0.ActiveSheet.Cells(lRow+offset, 2).value := card5
	wb0.ActiveSheet.Cells(lRow+offset, 3).value := card3 card4
	wb0.ActiveSheet.Cells(lRow+offset, 4).value := card2
	wb0.ActiveSheet.Cells(lRow+offset, 5).value := card1
}

wb0.Save()
wb1.Close()
wb0.Close()
xl.Quit()
xl := ""
return
GuiClose:
ExitApp

Looks like it works in my small tests.

*Edit: Changed the last A row calc to match the method you originally used for the last B row for consistency.
*Edit2: Made a change to accomodate more than 100 rows in A.

Posted: **09 Jun 2017, 08:42**

Hi Nightwolf85,

PERFECT !
THANK YOU VERY MUCH.

AutoHotkey Community

Excel

Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel

Re: Excel