A Quarry Regarding M S Excel Com Object. Needs Help.

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
Sabestian Caine
Posts: 528
Joined: 12 Apr 2015, 03:53

A Quarry Regarding M S Excel Com Object. Needs Help.

14 Dec 2017, 13:17

Hello friends

When i use following codes then they work fine and show the value of active cell in the msgbox-

Code: Select all

f1::
xl := ComObjActive("Excel.Application")
msgbox % xl.activecell.value
return
But when i use these codes then then they show error-

Code: Select all

f2::
xl2:= ComObjGet("C:\Users\htc\Desktop\book1.xlsx")
MsgBox % xl2.activecell.value
return

error window is this-

Image

I do not see why the above codes are not working?

I want to use comobjget as want to link the object with the specific excel sheet. in other cases comobjget is working fine like-
it successfully shows the value of cell no a1 in the below codes-

Code: Select all

f3::
xl2:= ComObjGet("C:\Users\htc\Desktop\book1.xlsx")
MsgBox % xl2.sheets("sheet1").range("a1").value
return
please help me regarding that and tell why this is happening. Thanks a lot all......
I don't normally code as I don't code normally.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

14 Dec 2017, 19:12

In general it is a bad idea to use ComObjGet when working with Excel.

The main problem is that ComObjGet does not return an application object like ComObjCreate or ComObjActive.

It returns a Workbook object which is not as versatile. For your case a workbook does not have an activecell. An application has an activecell. The list goes on. I use Com a lot with Excel but never use ComObjGet. It provides only a small shortcut in limited situations when an application level object is not needed.

Below is some equivalent code showing how you can work backwards from a workbook object to get an application object.

Code: Select all

F12::
	xlWorkbook := ComObjGet(A_Desktop "\Test\Test.xlsx")
	xlApp := xlWorkbook.Parent
	xlApp.Visible := true		; for testing
	xlApp.Windows(xlWorkbook.Name).Visible := true	; ComObjGet is invisible by default, an invisible workbook does not have an activecell
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return

F11::
	xlApp := ComObjCreate("Excel.Application")
	xlApp.Visible := true		; for testing
	xlWorkbook := xlApp.Workbooks.Open(A_Desktop "\Test\Test.xlsx")
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
Sabestian Caine
Posts: 528
Joined: 12 Apr 2015, 03:53

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

15 Dec 2017, 12:07

FanaticGuru wrote:In general it is a bad idea to use ComObjGet when working with Excel.

The main problem is that ComObjGet does not return an application object like ComObjCreate or ComObjActive.

It returns a Workbook object which is not as versatile. For your case a workbook does not have an activecell. An application has an activecell. The list goes on. I use Com a lot with Excel but never use ComObjGet. It provides only a small shortcut in limited situations when an application level object is not needed.

Below is some equivalent code showing how you can work backwards from a workbook object to get an application object.

Code: Select all

F12::
	xlWorkbook := ComObjGet(A_Desktop "\Test\Test.xlsx")
	xlApp := xlWorkbook.Parent
	xlApp.Visible := true		; for testing
	xlApp.Windows(xlWorkbook.Name).Visible := true	; ComObjGet is invisible by default, an invisible workbook does not have an activecell
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return

F11::
	xlApp := ComObjCreate("Excel.Application")
	xlApp.Visible := true		; for testing
	xlWorkbook := xlApp.Workbooks.Open(A_Desktop "\Test\Test.xlsx")
	MsgBox % xlApp.ActiveCell.Value
	xlApp.Quit()
return
FG
hello dear fanaticGuru... Long time since i heard from your side...

dear FG please tell me.. how can i associate object with specific excel workbook? if i simply use xl := ComObjActive("Excel.Application") then it works for most recently active workbook. for example if there are two excel workbooks are opened, suppose they are book1.xlsx and book2.xlsx, then the most recently active workbook's cell no a1's content can be seen by these codes-

Code: Select all

f1::
xl := ComObjActive("Excel.Application")
msgbox % xl.range("a1").value
return
while i want that whether any of book1.xlsx or book2.xlsx is active, it should always show the content of cell no a1 in book2.xlsx then i have to use comobjget. Following codes works fine in this case-

Code: Select all

f1::
xl2:= ComObjGet("C:\Users\htc\Desktop\book2.xlsx")
MsgBox % xl2.sheets("sheet1").range("a1").value
return
in the above codes i have specified the excel workbook by providing full path. moreover i have also specified the sheet number. Could you please tell me any better way to handle the specific workbooks by com object? please help.... thanks a lot...
I don't normally code as I don't code normally.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

15 Dec 2017, 15:05

Sabestian Caine wrote:

Code: Select all

f1::
xl2:= ComObjGet("C:\Users\htc\Desktop\book2.xlsx")
MsgBox % xl2.sheets("sheet1").range("a1").value
return
in the above codes i have specified the excel workbook by providing full path. moreover i have also specified the sheet number. Could you please tell me any better way to handle the specific workbooks by com object? please help.... thanks a lot...
Basically just like I showed in my previous example code.

You create an application object and then create a workbook object from that application object.

Code: Select all

xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true		; for testing
xlWorkbook := xlApp.Workbooks.Open(A_Desktop "\Test\Test.xlsx")
MsgBox % xlWorkbook.Sheets("Sheet1").Range("A1").Value
xlApp.Quit()
You can have a handle to as many workbooks as you want.

Code: Select all

xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true		; for testing
xlWorkbook1 := xlApp.Workbooks.Open(A_Desktop "\Test\Test1.xlsx")
xlWorkbook2 := xlApp.Workbooks.Open(A_Desktop "\Test\Test2.xlsx")
MsgBox % xlWorkbook1.Sheets("Sheet1").Range("A1").Value
MsgBox % xlWorkbook2.Sheets("Sheet1").Range("A1").Value
MsgBox % xlApp.ActiveCell.Value
xlApp.Quit()
This will open two workbooks with separate handles, get value of A1 from both and value of the activecell. The activecell will be from the last workbook opened.

Workbooks do not technically have an activecell. Excel could have a hundred workbooks open but only the one cell that Excel is currently ready to receive input into is the activecell of Excel.

It is also a little deceiving to name a workbook object xl2. That gives the impression that this is an Excel application object which it is not. If you want to keep it short like that I would at least call it wb2 to make it more clear this is a workbook. They are separate types of object with different properties and methods.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

16 Dec 2017, 12:26

- I recently found out that with the Excel_Get function, by specifying the 2nd parameter, you can latch onto a specific workbook via a control (e.g. EXCEL71, EXCEL72).
- Clearly, ActiveWorkbook will remain consistent whatever you do. However, after you've latched on, and got the object, is there anyway of retrieving, via the object, which workbook you originally latched on to? If there is not, then is there any point to having the 2nd parameter?

Code: Select all

;[Excel_Get function]
;MS-Office-COM-Basics/Excel_Get.ahk at master · ahkon/MS-Office-COM-Basics · GitHub
;https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk

q:: ;excel - latch onto workbook via control
WinGet, hWnd, ID, A

oXl := Excel_Get("ahk_id " hWnd, 1)
MsgBox, % oXl.ActiveWorkbook.Name
oXl := ""

oXl := Excel_Get("ahk_id " hWnd, 2)
MsgBox, % oXl.ActiveWorkbook.Name
oXl := ""
return
Link:
[latch onto a specific workbook]
Ask if a control is active vs a window - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 49#p188049
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

18 Dec 2017, 14:19

jeeswg wrote:- I recently found out that with the Excel_Get function, by specifying the 2nd parameter, you can latch onto a specific workbook via a control (e.g. EXCEL71, EXCEL72).
You might find this GetExcel class useful in some situations.
https://autohotkey.com/boards/viewtopic ... 76#p134876

Code: Select all

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

; FanaticGuru example of GetExcel class method Xls
Xls := GetExcel.Xls()
  for index, Xl in Xls
	for Workbook in Xl.Workbooks
		MsgBox %  Workbook.name "`n" Workbook.Windows(1).Visible

; FanaticGuru example of GetExcel class method Wbs
Wbs := GetExcel.Wbs(,1) ; only visible workbooks
for index, Wb in Wbs
		MsgBox %  Wb.Name "`n" Wb.Windows(1).Visible

class GetExcel
{
	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 := GetExcel.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
}
Unlike the Excel_Get function that goes from a window to the COM object associated with that window this class queries the table of all active COM objects and then finds the ones that are Excel objects. It is preliminary for cases when you have more than one application of Excel running and you need to access all of them or a specific one. It allows you to easily get all Workbooks that match a needle supplied regardless of how many applications of Excel are running. ie You can have 5 workbooks in 1 application of Excel or 5 applications of Excel running with 1 workbook each. Does not matter, it will find the workbook you are looking for based on a regex needle.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: A Quarry Regarding M S Excel Com Object. Needs Help.

18 Dec 2017, 14:39

- Thanks very much for your response FanaticGuru. There is some very interesting information there, including some powerful general techniques.
- What remains is a question that is more out of interest. The 2nd parameter of the Excel_Get function, AFAIK, cannot be useful, because whatever control you specify, (a) you end up with the same object, (b) there is no way to know which workbook (control) you latched onto. I would be happy to be contradicted on this, but right now I cannot see that the 2nd parameter serves a purpose. Thanks.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Google [Bot], sbrady19 and 121 guests