Hi,
I have tried using
xl := ComObjGet("Book1")
But this is not working! It gives me a syntax error, and i haven't been able to figure out a way to do this.
All I want to do is create multiple objects like, xl1, xl2, xl3 to handle 3 different excel files that i have open. Please help.
Thanks
Handle multiple open Excel files with ComObj Topic is solved
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
FanaticGuru wrote: ↑10 Feb 2019, 19:35
A function I wrote for this purpose and more:It will start Excel if needed and open the file if needed. Can also activate a specific sheet.Code: Select all
; [Function] Excel_GetWB ; Fanatic Guru ; 2019 01 28 ; ; Get Excel Workbook COM object ; start Excel and open WB if needed ; ;{----------------------------------------------- ; ; Excel_GetWB(Path, Sheet, Visible) ; ; Parameters: ; Path path or file name of Excel workbook ; Sheet sheet name or index number to activate (Default: ActiveSheet) ; Visible visible state (Default: true) ; "" results in current visible state with no change ; ; Example: ; xlWB := Excel_GetWB(A_Desktop "\Test\specificExcelFile.xlsx", "Sheet4") ; ;} Excel_GetWB(Path, Sheet := "ActiveSheet", Visible := true) { SplitPath, Path, FileName try { xlApp := ComObjActive("Excel.Application") for xlWB in xlApp.Workbooks if (xlWB.Name = FileName) { Found := true break } } catch xlApp := ComObjCreate("Excel.Application") if !Found xlWB := xlApp.Workbooks.Open(Path) xlWB.Activate if (Sheet != "ActiveSheet") try xlWB.Sheets(Sheet).Activate if (Visible != "") xlApp.Visible := Visible return xlWB }
FG
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
Thanks for the reply.
It's working! But it activates the Excel window while switching to the required sheet, is there any way to get all this done without activating the excel window and while, say i'm working on Chrome?
EDIT: Got it, removed the
xlWB.Activate
command.
But, later when I try to do xlWB.ActiveCell it gives me an error.
The function is returning xlWB which is an Object right? So I should be able to use it normally as I would use xl := ComObjActive("Excel.Application") isn't it?
It's working! But it activates the Excel window while switching to the required sheet, is there any way to get all this done without activating the excel window and while, say i'm working on Chrome?
EDIT: Got it, removed the
xlWB.Activate
command.
But, later when I try to do xlWB.ActiveCell it gives me an error.
The function is returning xlWB which is an Object right? So I should be able to use it normally as I would use xl := ComObjActive("Excel.Application") isn't it?
Re: Handle multiple open Excel files with ComObj
In this case you could delete or comment out the following lines from the function:
Not tested, but I think it should also work without the xlWB.Activate command
Edit: My proposal was a wrong quickshot.
When you comment out "xlWB.Activate":
The activation of the Excel window, should happen only once, when Excel is started and xlApp.Visible := true.
Not tested, but I think it should also work without the xlWB.Activate command
Code: Select all
;xlWB.Activate
;this lines are needed
if (Sheet != "ActiveSheet")
try
xlWB.Sheets(Sheet).Activate
if (Visible != "")
xlApp.Visible := Visible
When you comment out "xlWB.Activate":
The activation of the Excel window, should happen only once, when Excel is started and xlApp.Visible := true.
Last edited by aifritz on 24 Aug 2019, 03:09, edited 2 times in total.
Re: Handle multiple open Excel files with ComObj
Code: Select all
wb1 := ComObjGet("D:\Desktop\1.xlsx")
wb2 := ComObjGet("D:\Desktop\2.xlsx")
MsgBox % wb1.ActiveSheet.Range("A1").Text
MsgBox % wb2.ActiveSheet.Range("A1").Text
wb1.Close()
wb2.Close()
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
I understand, but then after calling this function with the required Workbook and Sheet names, when I try to use cell := xlWB.ActiveCell, it gives me an error. That's what i want to know. the returned object from the function is not working the same way as when you define xl := ComObjActive(excel.application)aifritz wrote: ↑23 Aug 2019, 13:33In this case you could delete or comment out the following lines from the function:
Not tested, but I think it should also work without the xlWB.Activate commandEdit: My proposal was a wrong quickshot.Code: Select all
;xlWB.Activate ;this lines are needed if (Sheet != "ActiveSheet") try xlWB.Sheets(Sheet).Activate if (Visible != "") xlApp.Visible := Visible
When you comment out "xlWB.Activate":
The activation of the Excel window, should happen only once, when Excel is started and xlApp.Visible := true.
Can you try doing, xlWB.ActiveCell after you call the function and try to manipulate the workbook using the returned object from the function?
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
This is working perfectly! But when I try to do the following thing it gives me an error for new_cell.selecttmplinshi wrote: ↑23 Aug 2019, 13:35Not sure if this helpsCode: Select all
wb1 := ComObjGet("D:\Desktop\1.xlsx") wb2 := ComObjGet("D:\Desktop\2.xlsx") MsgBox % wb1.ActiveSheet.Range("A1").Text MsgBox % wb2.ActiveSheet.Range("A1").Text wb1.Close() wb2.Close()
Code: Select all
wb1 := ComObjGet(A_Desktop "\1.xlsx")
wb2 := ComObjGet(A_Desktop "\2.xlsx")
MsgBox % wb1.ActiveSheet.Range("A1").Text
MsgBox % wb2.ActiveSheet.Range("A1").Text
last_cell := wb1.Sheets("Sheet1").Range("A:A").SpecialCells(11).row
new_cell := wb1.Sheets("Sheet1").Range("A" last_cell+1)
new_cell.Select
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
1) Is there any way to use ComObjGet with just the name of the excel workbook that's open, because I don't want to restrict the user to place his excel at a specified directory only. If I can just create two handles of excel objects, link them to 2 open workbooks and manipulate both of them at the same time using those objects without worrying about switching the workbooks that would be best.
2) The function
the xlWB that it returns, is it the same as xl := ComObjActive("Excel.Application") ?
2) The function
Code: Select all
Excel_GetWB(Path, Sheet := "ActiveSheet", Visible := True)
{
SplitPath, Path, FileName
try
{
xlApp := ComObjActive("Excel.Application")
for xlWB in xlApp.Workbooks
if (xlWB.Name = FileName)
{
Found := true
break
}
}
catch
xlApp := ComObjCreate("Excel.Application")
; if !Found
; xlWB := xlApp.Workbooks.Open(Path)
; xlWB.Activate
if (Sheet != "ActiveSheet")
try
xlWB.Sheets(Sheet).Activate
if (Visible != "")
xlApp.Visible := Visible
return xlWB
}
Re: Handle multiple open Excel files with ComObj Topic is solved
You can't select cell without activating the sheet.
Some codes that might help:
Some codes that might help:
Code: Select all
xl := ComObjActive("Excel.Application")
MsgBox % xl.application.Workbooks.Count()
for wb in xl.application.Workbooks
MsgBox % wb.FullName
wb1 := xl.application.Workbooks.Item(1)
wb2 := xl.application.Workbooks.Item(2)
MsgBox % wb1.FullName
MsgBox % wb2.FullName
; wb1 := xl.application.Workbooks.Item("1.xlsx")
; wb2 := xl.application.Workbooks.Item("2.xlsx")
; MsgBox % wb1.FullName
; MsgBox % wb2.FullName
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
Code: Select all
last_cell := wb2.Sheets("Sheet1").Range("A:A").SpecialCells(11).row
new_cell := wb2.Sheets("Sheet1").Range("A" last_cell+1)
new_cell.value := var_1
i'm trying to add the value of var_1 to the next row in column A without using the select function. Still its not working.
Re: Handle multiple open Excel files with ComObj
works fine for me.
Code: Select all
xl := ComObjActive("Excel.Application")
wb1 := xl.application.Workbooks.Item(1)
wb2 := xl.application.Workbooks.Item(2)
MsgBox % wb2.FullName
last_cell := wb2.Sheets("Sheet1").Range("A:A").SpecialCells(11).row
new_cell := wb2.Sheets("Sheet1").Range("A" last_cell+1)
new_cell.value := "new value"
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
This Item(1) and (2), is it dependent on the level of the Excel window in the stack of active windows? Like which one is on top, workbook 1 or 2? If so then is there a way to not depend on that? Is there a way to create wb1 and wb2 based on the Names of the workbooks?tmplinshi wrote: ↑26 Aug 2019, 04:55works fine for me.Code: Select all
xl := ComObjActive("Excel.Application") wb1 := xl.application.Workbooks.Item(1) wb2 := xl.application.Workbooks.Item(2)
Re: Handle multiple open Excel files with ComObj
See my previous reply #p289573 above
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
tmplinshi wrote: ↑23 Aug 2019, 13:35Not sure if this helpsCode: Select all
wb1 := ComObjGet("D:\Desktop\1.xlsx") wb2 := ComObjGet("D:\Desktop\2.xlsx") MsgBox % wb1.ActiveSheet.Range("A1").Text MsgBox % wb2.ActiveSheet.Range("A1").Text wb1.Close() wb2.Close()
Code: Select all
#k::
wb1 := ComObjGet(A_Desktop "\1.xlsx")
wb2 := ComObjGet(A_Desktop "\2.xlsx")
MsgBox % wb1.ActiveSheet.Range("A1").Text
MsgBox % wb2.ActiveSheet.Range("A1").Text
last_cell := wb1.Sheets("DE Doc").Range("A:A").SpecialCells(11).row
new_cell := wb1.Sheets("DE Doc").Range("A" last_cell+1)
new_cell.value := "New value for workbook 1"
wb2.ActiveSheet.Range("A2").value := "New for wb2"
; wb1.saveas(A_Desktop "\new1.xlsx") ; This saves a new workbook but with nothing in it
; wb2.saveas(A_Desktop "\new2.xlsx")
wb1.Close()
wb2.Close()
return
Re: Handle multiple open Excel files with ComObj
It's amazing how many examples are on the AHK forums that make this same mistake. You need to clear vars that contain COM objects when you are done with them.ankitkraken wrote: ↑07 Sep 2019, 13:06When i try to edit the workbooks without opening them, it shows a black workbook when i open then normally by double-clicking on them, and it doesn't let me save it either and gives an error that the workbooks are already open. Is there something wrong with this code or does wb1.Close() not working?Code: Select all
#k:: wb1 := ComObjGet(A_Desktop "\1.xlsx") wb2 := ComObjGet(A_Desktop "\2.xlsx") MsgBox % wb1.ActiveSheet.Range("A1").Text MsgBox % wb2.ActiveSheet.Range("A1").Text last_cell := wb1.Sheets("DE Doc").Range("A:A").SpecialCells(11).row new_cell := wb1.Sheets("DE Doc").Range("A" last_cell+1) new_cell.value := "New value for workbook 1" wb2.ActiveSheet.Range("A2").value := "New for wb2" ; wb1.saveas(A_Desktop "\new1.xlsx") ; This saves a new workbook but with nothing in it ; wb2.saveas(A_Desktop "\new2.xlsx") wb1.Close() wb2.Close() return
Code: Select all
;...
wb1.Close()
wb2.Close()
MsgBox Check your Task Manager. The Excel process is probably still running.
; The Excel process will not close if you don't clear the variables that contain COM objects
wb1 := wb2 := new_cell := ""
MsgBox Now check your Task Manager now that you have cleared the variables that reference Excel objects. The Excel process should be closed.
- ankitkraken
- Posts: 84
- Joined: 01 Jun 2019, 08:47
- Location: India
Re: Handle multiple open Excel files with ComObj
Thank you for the reply. Yes i see that clearing the objects is killing the excel application. But i'm still unable to edit the workbooks and save them, whether it's overwriting or saving as new.
Re: Handle multiple open Excel files with ComObj
Not for me. wb1.Close() successfully closed EXCEL.EXE process.Datapoint wrote: ↑07 Sep 2019, 16:52It's amazing how many examples are on the AHK forums that make this same mistake. You need to clear vars that contain COM objects when you are done with them.Code: Select all
;... wb1.Close() wb2.Close() MsgBox Check your Task Manager. The Excel process is probably still running. ; The Excel process will not close if you don't clear the variables that contain COM objects wb1 := wb2 := new_cell := ""
Code: Select all
f9::
wb1 := ComObjGet(A_Desktop "\1.xlsx")
MsgBox % wb1.ActiveSheet.Range("A1").Text
wb1.Close()
return
Who is online
Users browsing this forum: No registered users and 227 guests