Handle multiple open Excel files with ComObj Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
ankitkraken
Posts: 54
Joined: 01 Jun 2019, 08:47

Handle multiple open Excel files with ComObj

23 Aug 2019, 10:11

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
aifritz
Posts: 144
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Handle multiple open Excel files with ComObj

23 Aug 2019, 10:19

FanaticGuru wrote:
10 Feb 2019, 19:35
Sanjay wrote:
10 Feb 2019, 10:36
I have multiple excel files opened at the same time. For example A.xlsx, B.xlsx, C.xlsx, D.xlsx.
I want to get handle to only one specific excel file (A.xlsx) and then add data into or get data from it.

A function I wrote for this purpose and more:

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
}
It will start Excel if needed and open the file if needed. Can also activate a specific sheet.

FG
ankitkraken
Posts: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

23 Aug 2019, 11:43

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?
aifritz
Posts: 144
Joined: 29 Jul 2018, 11:30
Location: Germany

Re: Handle multiple open Excel files with ComObj

23 Aug 2019, 13:33

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 :?

Code: Select all

;xlWB.Activate
;this lines are needed
	if (Sheet != "ActiveSheet")
		try
			xlWB.Sheets(Sheet).Activate
	if (Visible != "")
		xlApp.Visible := Visible
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.
Last edited by aifritz on 24 Aug 2019, 03:09, edited 2 times in total.
tmplinshi
Posts: 1496
Joined: 01 Oct 2013, 14:57

Re: Handle multiple open Excel files with ComObj

23 Aug 2019, 13:35

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()
Not sure if this helps
ankitkraken
Posts: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

24 Aug 2019, 03:32

aifritz wrote:
23 Aug 2019, 13:33
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 :?

Code: Select all

;xlWB.Activate
;this lines are needed
	if (Sheet != "ActiveSheet")
		try
			xlWB.Sheets(Sheet).Activate
	if (Visible != "")
		xlApp.Visible := Visible
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.
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)
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: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

24 Aug 2019, 03:40

tmplinshi wrote:
23 Aug 2019, 13:35

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()
Not sure if this helps
This is working perfectly! But when I try to do the following thing it gives me an error for new_cell.select

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
With this i'm selecting the first empty cell in Col A
ankitkraken
Posts: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

24 Aug 2019, 03:46

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

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
}
the xlWB that it returns, is it the same as xl := ComObjActive("Excel.Application") ?
tmplinshi
Posts: 1496
Joined: 01 Oct 2013, 14:57

Re: Handle multiple open Excel files with ComObj  Topic is solved

24 Aug 2019, 07:28

You can't select cell without activating the sheet.

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: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

26 Aug 2019, 04:30

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
With this code,
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.
tmplinshi
Posts: 1496
Joined: 01 Oct 2013, 14:57

Re: Handle multiple open Excel files with ComObj

26 Aug 2019, 04:55

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: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

03 Sep 2019, 09:59

tmplinshi wrote:
26 Aug 2019, 04:55
works fine for me.

Code: Select all

xl := ComObjActive("Excel.Application")

wb1 := xl.application.Workbooks.Item(1)
wb2 := xl.application.Workbooks.Item(2)
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?
ankitkraken
Posts: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

07 Sep 2019, 13:06

tmplinshi wrote:
23 Aug 2019, 13:35

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()
Not sure if this helps

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
When 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?
User avatar
Datapoint
Posts: 68
Joined: 18 Mar 2018, 17:06

Re: Handle multiple open Excel files with ComObj

07 Sep 2019, 16:52

ankitkraken wrote:
07 Sep 2019, 13:06

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
When 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?
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.

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: 54
Joined: 01 Jun 2019, 08:47

Re: Handle multiple open Excel files with ComObj

08 Sep 2019, 01:18

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.
tmplinshi
Posts: 1496
Joined: 01 Oct 2013, 14:57

Re: Handle multiple open Excel files with ComObj

09 Sep 2019, 04:10

Datapoint wrote:
07 Sep 2019, 16:52
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.

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 := ""
Not for me. wb1.Close() successfully closed EXCEL.EXE process.

Code: Select all

f9::
wb1 := ComObjGet(A_Desktop "\1.xlsx")
MsgBox % wb1.ActiveSheet.Range("A1").Text
wb1.Close()
return

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], flyingDman, Google [Bot] and 222 guests