4 replies to this topic

[jeby]

I am seeing that the ComObjActive("Excel.Application") command is only creating a handle for workbooks contained in the first instance of Excel.  It is very often useful for me to compare Excel sheets side-by-side, which is why I find myself starting multiple instances of Excel.

 

These lines of script are what I have used to see this symptom:

Xl := ComObjActive("Excel.Application") 		;handle
Xl.Visible := True 				;by default excel sheets are invisible
Xl.Range("A1").Value := "hello world!" 		;set cell 'A1' to a string

Let's say there are two instances of Excel.  Instance one has two workbooks open (WB1-1, WB1-2) and instance two only has one workbook (WB2).  If I give WB1-1 focus, then give WB2 focus, then use the hotkey, "hello world" is written to WB1-1.  If I give WB1-2 focus, then give WB2 focus, then use the hotkey, "hello world" is written to WB1-2.

 

Has anybody seen similar behavior and/or know how to get around this?  BTW, I am using Excel 2010 on Windows 7.

[Lexikos]

Try GetActiveObjects.

[jeby]

Thanks for the suggestion Lexikos.  When I put this code into my .ahk file I get six entries in the message box, and only the bottom three are for open Excel workbooks:

• Book1 -- _Workbook
• Book2 -- _Workbook
• C:\Users\jeby\Documents\Project Tasks.xlsx -- _Workbook

After seeing this, I think my understanding of active objects is off because I thought there was only one active object at any given time (whatever program currently has "focus").  If I am using the word "focus" in the right context, then my goal is actually to create a com object associated with the Excel workbook in focus.  One thought I have is to use the list that your function creates to cycle through each active "Workbook" type com object and check if that object has the focus.  This would require that a property such as "focus", or a function such as "getFocus()" exists for com objects and I have had a difficult time finding anything.

 

So maybe a good intermediate question is, am I looking in the right place by looking at the Microsoft MSDN Microsoft.Office.Interop.Excel (https://msdn.microso...on_members.aspx)?  Looking in here, I don't see anything that points to grabbing the workbook that is in focus.  I have tried creating new objects using .ActiveWorkbook and .ActiveSheet, but it still results in an object that points to the wrong workbook (this is what I expected).

 

Am I missing a property or a function that will tell me if a com object has focus?

[Alpha Bravo]

don't confuse yourself about which book has focus, just assign individual objects for each open workbook regardless of its instance like so:

oWB1 := ComObjGet("Book1") ; or in your case "WB1-1"
oWB2 := ComObjGet("Book2") ; or "WB1-2"
oWB3 := ComObjGet("Book3") ; or "WB2"

oWB1.ActiveSheet.Range("A1").value := "book1"
oWB2.ActiveSheet.Range("A1").value := "book2"
oWB3.ActiveSheet.Range("A1").value := "book3"

Msgbox % 	oWB1.ActiveSheet.Range("A1").value
. "`n" 		oWB2.ActiveSheet.Range("A1").value
. "`n" 		oWB3.ActiveSheet.Range("A1").value

[jeby]

Yeah, maybe I should have explained more what I am trying to do.  Basically, I would like to create a macro such that if I highlight two groups of cells on a sheet (let's say A1:D1 and A3:D3, for example), I can use the shortcut key to swap the contents of the groups of cells.  I want this to work on whatever sheet has the focus, and so don't actually know the name of that workbook or sheet ahead of time.  That's why I need to figure out which one has focus.

 

I am contemplating trying my hand at creating a macro workbook in Excel that is tied to my user and not a specific workbook.  I would still rather do it in AHK, however, because I have accumulated a handful of macros for various things and would like to keep them all in one spot.  This would help in changing/wiping computers as all I would have to do is copy the .ahk file from My Documents instead of still doing that but also figuring out where my "global" Excel macro is stored and copying that, too.