Issues working with two Excel files

[codude]

Hello, I am working with two XL files (Book1 and Book2) in some script and if book 1 was the active screen this was working fine.

Code: Select all

oExcel := ComObjActive("Excel.Application")
Row := oExcel.ActiveCell.Row
ItemTDV := oExcel.Cells(Row, 1).text
PatternItemIDV := oExcel.Cells(Row, 2).text
ExcelModelV := oExcel.Cells(Row, 3).text
ExcelTitleV := oExcel.Cells(Row, 4).text
DiscriptionV := oExcel.Cells(Row, 7).text

[Mod edit: [code][/code] tags added.]

I made several changes and it seems now even if Book1 is the active screen, my script is pulling the information from Book2. I have researched five or six methods for correcting this but with little knowledge of how this all works no matter what I do it seems I can’t apply these solutions to my particular script. If that matters I am using Excel 10 and the full names of the XL files are “Microsoft Excel – Book1” and “Microsoft Excel – Book1” and I am running each independent of each other (each can be open in its own screen)… Thanks

[FanaticGuru]

You are letting Excel default to Workbooks and Sheets but you can specify what Workbook and Sheet you want to use like this:

Code: Select all

ItemTDV := oExcel.Workbooks("Book1").ActiveSheet.Cells(Row, 1).text ; Workbook named "Book1", Active Sheet
ItemTDV := oExcel.Workbooks(2).Sheets(3).Cells(Row, 1).text ; Second Workbook, Third Sheet

This is assuming you only have one instance of Excel running (which is the preferred way). If you truly have two totally separate processes of Excel running then you have to sort them out as ComObjActive("Excel.Application") will not let you access both of them. In Window's terms both running processes of Excel are "active", meaning they have entries in the COM table.

The newest Excel can look like you have two processes running by the way the Windows look with their own toolbars and such but usually only one process of Excel is actually running and registered in the COM table. It is more a style decision of Excel of how to display multiple workbooks.

FG

[Tigerlily]

You are letting Excel default to Workbooks and Sheets but you can specify what Workbook and Sheet you want to use like this:

Code: Select all

ItemTDV := oExcel.Workbooks("Book1").ActiveSheet.Cells(Row, 1).text ; Workbook named "Book1", Active Sheet
ItemTDV := oExcel.Workbooks(2).Sheets(3).Cells(Row, 1).text ; Second Workbook, Third Sheet

This is assuming you only have one instance of Excel running (which is the preferred way). If you truly have two totally separate processes of Excel running then you have to sort them out as ComObjActive("Excel.Application") will not let you access both of them. In Window's terms both running processes of Excel are "active", meaning they have entries in the COM table.

The newest Excel can look like you have two processes running by the way the Windows look with their own toolbars and such but usually only one process of Excel is actually running and registered in the COM table. It is more a style decision of Excel of how to display multiple workbooks.

FG

What is the COM table and how would one access it?

[Datapoint]

I am running each independent of each other (each can be open in its own screen)…

Instead of: oExcel := ComObjActive("Excel.Application")
try: oExcel := Excel_Get()

- Excel_Get

What is the COM table and how would one access it?

https://www.autohotkey.com/boards/viewtopic.php?p=134048#p134048

[FanaticGuru]

What is the COM table and how would one access it?

The COM table is basically a list of applications that register with Windows saying that they want to let other applications communicate with them and here is their number to call them. In short it is a dating site for applications that Windows runs to help programs hookup and create lots of little subroutines.

Here is a script what will let you see all Active COM objects and also a function to get a connection to all Workbooks regardless of if they are open in separate processes of Excel.

Code: Select all

; Lexikos example to get all active objects
list := "All Active COM Objects`n"
for name, obj in GetActiveObjects()
    list .= name " -- " ComObjType(obj, "Name") "`n"
MsgBox %list%

; FanaticGuru example of Excel_Objects class method Xls
list := "All Excel Workbooks and Visible State`n"
Xls := Excel_Objects.Xls() ; Get all Excel objects
  for index, Xl in Xls
	for Workbook in Xl.Workbooks
		list .= Workbook.name "`t" Workbook.Windows(1).Visible "`n"
MsgBox %list%

; FanaticGuru example of Excel_Objects class method Wbs
list := "All Visible Excel Workbooks and Visible State`n"
Wbs := Excel_Objects.Wbs(,1) ; only visible workbooks
for index, Wb in Wbs
		list .= Wb.Name "`t" Wb.Windows(1).Visible "`n"
MsgBox %list%

Esc::ExitApp

class Excel_Objects
{
	Xls(Needle := "")
	{
		Xls := {}
		for index, obj in GetActiveObjects()
		{
			try 
				Name := obj.Application.ActiveWorkbook.Name
			catch
				continue
			if (RegExMatch(index, "(^|\\)" Name  "$") and RegExMatch(Name, Needle))
				Xls.Push(obj.Application)
		}
		return Xls
	}
	Wbs(Needle := "", Visible := "")
	{
		Wbs := {}
		Xls := Excel_Objects.Xls()
		for index, Xl in Xls
			for Workbook in Xl.Workbooks
				if RegExMatch(Workbook.Name, Needle)
					if (Visible = 1 and Workbook.Windows(1).Visible)
						Wbs.Push(Workbook)
					else if  (Visible = 0 and not Workbook.Windows(1).Visible)
						Wbs.Push(Workbook)
					else if (Visible = "")
						Wbs.Push(Workbook)
		return Wbs
	}
}

; GetActiveObjects v1.0 by Lexikos
; http://ahkscript.org/boards/viewtopic.php?f=6&t=6494
GetActiveObjects(Prefix:="", CaseSensitive:=false) {
    objects := {}
    DllCall("ole32\CoGetMalloc", "uint", 1, "ptr*", malloc) ; malloc: IMalloc
    DllCall("ole32\CreateBindCtx", "uint", 0, "ptr*", bindCtx) ; bindCtx: IBindCtx
    DllCall(NumGet(NumGet(bindCtx+0)+8*A_PtrSize), "ptr", bindCtx, "ptr*", rot) ; rot: IRunningObjectTable
    DllCall(NumGet(NumGet(rot+0)+9*A_PtrSize), "ptr", rot, "ptr*", enum) ; enum: IEnumMoniker
    while DllCall(NumGet(NumGet(enum+0)+3*A_PtrSize), "ptr", enum, "uint", 1, "ptr*", mon, "ptr", 0) = 0 ; mon: IMoniker
    {
        DllCall(NumGet(NumGet(mon+0)+20*A_PtrSize), "ptr", mon, "ptr", bindCtx, "ptr", 0, "ptr*", pname) ; GetDisplayName
        name := StrGet(pname, "UTF-16")
		DllCall(NumGet(NumGet(malloc+0)+5*A_PtrSize), "ptr", malloc, "ptr", pname) ; Free
        if InStr(name, Prefix, CaseSensitive) = 1 {
            DllCall(NumGet(NumGet(rot+0)+6*A_PtrSize), "ptr", rot, "ptr", mon, "ptr*", punk) ; GetObject
            ; Wrap the pointer as IDispatch if available, otherwise as IUnknown.
            if (pdsp := ComObjQuery(punk, "{00020400-0000-0000-C000-000000000046}"))
                obj := ComObject(9, pdsp, 1), ObjRelease(punk)
            else
                obj := ComObject(13, punk, 1)
            ; Store it in the return array by suffix.
            objects[SubStr(name, StrLen(Prefix) + 1)] := obj
        }
        ObjRelease(mon)
    }
    ObjRelease(enum)
    ObjRelease(rot)
    ObjRelease(bindCtx)
    ObjRelease(malloc)
    return objects
}

FG

[Tigerlily]

What is the COM table and how would one access it?

In short it is a dating site for applications that Windows runs to help programs hookup and create lots of little subroutines.

Thanks FG - this is great. I've been working on a script that manages this issue by putting all the workbooks that are not in the "active" instance of Excel into the active instance. I will check this out and see if this will help me accomplish that. Although, I suspect you have probably already done some leg-work regarding that annoyance.

[codude]

Hello, sorry I’ve been slow to respond to these solutions here but I got called out of town. After reading the 1st post from FG I tried running Book1 and Book2 in just one instance of Excel (I was running 2 before) and it seemed to solve the problem. This worked for an hour or so and then I started having problems with this.

Code: Select all

SetTitleMatchMode, 2
WinActivate, Book2
WinWaitActive, Book2
Sleep 100
MouseMove, % A_CaretX, % A_CaretY  
Sleep 50
if ErrorLevel   ; i.e. no Caret
{
MsgBox, Caret not found
Return
}  

I am NOT getting the “Catet not found message it’s just doing nothing. I found if I closed Book 1 it would then work fine. After restarting the computer it again worked fine but again stopped working later. I am also having instances where I am again losing the ability to select the text from a called out cell in an active book and again restarting seems to cure this also. To cure this problem I was going to try the code from FG’s first post but I’m not sure what to do with the “MouseMove” issue.

[codude]

It seems when I run both books with one instance of Excel just because one particular book is on the top screen, even a simple command like "MouseMove, % A_CaretX, % A_CaretY " needs to know which book to deal with. Is this correct?

[Datapoint]

If you are trying to click on a cell: Excel_Get().ActiveCell.Activate (Works on the active window, unlike ComObjActive. And also works in multiple excel instances.) Although it is kind of redundant to click on the active cell. Do you just want to change the value or something? I don't understand why you would want to click on it.
I would consider mouse clicks a last resort because they can be difficult to control as you have found out. In Excel you should be able to do pretty much everything with COM.

This worked for an hour or so and then I started having problems with this.

Does another script use ComObjCreate("Excel.Application")? Because that creates another instance of excel, and if you don't make it visible it will just stay open in the background and can interfere with ComObjActive (as the link i posted above explains). Check your task manager to see if you have more than one EXCEL.EXE process running - If you do, then ComObjActive will have problems.

[Haan]

You are letting Excel default to Workbooks and Sheets but you can specify what Workbook and Sheet you want to use like this:

Code: Select all

ItemTDV := oExcel.Workbooks("Book1").ActiveSheet.Cells(Row, 1).text ; Workbook named "Book1", Active Sheet
ItemTDV := oExcel.Workbooks(2).Sheets(3).Cells(Row, 1).text ; Second Workbook, Third Sheet

This is assuming you only have one instance of Excel running (which is the preferred way). If you truly have two totally separate processes of Excel running then you have to sort them out as ComObjActive("Excel.Application") will not let you access both of them. In Window's terms both running processes of Excel are "active", meaning they have entries in the COM table.

The newest Excel can look like you have two processes running by the way the Windows look with their own toolbars and such but usually only one process of Excel is actually running and registered in the COM table. It is more a style decision of Excel of how to display multiple workbooks.

FG

Your tip helped me a lot, thanks. I have a fully functional script, and I wasn't sure how to save the data on the second sheet.