Handle multiple open Excel files with ComObj Topic is solved

[ankitkraken]

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]

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]

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]

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.

[tmplinshi]

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]

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]

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]

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]

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]

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]

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]

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?

[tmplinshi]

See my previous reply #p289573 above

[ankitkraken]

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?

[Datapoint]

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]

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]

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