MS Office COM Basics

Helpful script writing tricks and HowTo's
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

24 Feb 2017, 01:15

JoeWinograd wrote:Hi FG,
That will be great stuff if I can get it to work, but so far haven't been able to. Here's what I tried:

Code: Select all

InputFile:="c:\temp\FGtest.xlsx"
CellNumber:="A1"
oExcel:=ComObjCreate("Excel.Application")
oWorkbook:=oExcel.Workbooks.Open(InputFile)
CellIsFormula:=oExcel.WorksheetFunction.IsFormula(CellNumber)
It says that it is unable to get the IsFormula property of the WorksheetFunction class:

What is wrong with the code above? Thanks, Joe
It is expecting a Range object not an address:

Code: Select all

Xl := ComObjActive("Excel.Application")
	MsgBox % Xl.WorksheetFunction.IsFormula(Xl.Range("A1"))
Or using your code change to CellNumber := oExcel.Range("A1") and of course put it after you create the oExcel.

It is also important to remember that Excel will return -1 as true.

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
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

24 Feb 2017, 01:33

JoeWinograd wrote:Hi kon and FG (or anyone else who wants to jump in),
Quick question regarding Office COM basics — what is the difference between ComObjActive and ComObjCreate? The documentation isn't clear to me, as the write-ups sound similar:
ComObjActive
Creates an object representing a typed value to be passed as a parameter or return value.

ComObjCreate
Creates a COM object.
Thanks, Joe
ComObjActive connects you to an Excel process that is already running.
ComObjCreate starts a new Excel process.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := true	
XlNewWB := Xl.Workbooks.Add
This will basically start Excel and create a new blank workbook.

If you don't make the Excel process visible with Xl.Visible := true it is important you don't create a bunch of invisible Excel processes and lose track of them. If you put the above code in a loop 100 and don't make them visible you will have a 100 processes of Excel running out there invisible even after the script ends. It is pretty obvious with a loop but I have seen it occur in a hotkey so that every time you push the hotkey a new Excel gets created but never gets closed properly.

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
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

24 Feb 2017, 07:00

FG wrote:It is expecting a Range object not an address
Thanks for that — working perfectly now!
User avatar
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

24 Feb 2017, 07:29

FG wrote:ComObjActive connects you to an Excel process that is already running.
How does it know which Excel process to connect to? I normally have just one ComObjCreate in a script — when would I want to use a ComObjActive statement?

I've written a number of scripts that process Excel and Word files. I never want Excel or Word to be visible — just want to read the contents of files and process them (so I never set the Visible property to True). But, of course, I don't want to leave a bunch of invisible Excel or Word processes out there. I normally do this in my Excel-based scripts before exiting:

Code: Select all

oWorkbook.Close(0)
oExcel.Quit
ObjRelease(oExcel)
And this in the Word-based ones:

Code: Select all

oDoc.Close(0)
oWord.Quit
ObjRelease(oWord)
Is that sufficient to prevent invisible Excel or Word processes? Thanks, Joe
User avatar
sinkfaze
Posts: 616
Joined: 01 Oct 2013, 08:01

Re: MS Office COM Basics

24 Feb 2017, 09:58

JoeWinograd wrote:How does it know which Excel process to connect to? I normally have just one ComObjCreate in a script — when would I want to use a ComObjActive statement?
It doesn't, but if you have only one Excel application running it's irrelevant. Any time one Excel application is already open and you do not have a handle to that instance, use ComObjActive().

IIRC, ObjRelease() isn't intended for this purpose; once your script hits its return (or exits) that handle to the application will be released. I believe you can omit that step.

You can always use the Process command to check for and close any instances after you've closed and quit, that will obviously be a brute method but it will be 100%. If what you have is working, don't change it.
User avatar
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

24 Feb 2017, 12:09

Thanks, sinkfaze, very helpful, but I'm still unclear on why I would want to have another Excel or Word instance in a script (via ComObjActive) when I already have one (via ComObjCreate).
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

24 Feb 2017, 14:17

JoeWinograd wrote:Thanks, sinkfaze, very helpful, but I'm still unclear on why I would want to have another Excel or Word instance in a script (via ComObjActive) when I already have one (via ComObjCreate).
If you are wanting to do stuff in the background without ever seeing Excel then you would probably never want to use ComObjActive. You create an Excel invisible, do some stuff, then close it.

Me, I use ComObjActive very often because I often have an Excel file open that I am working on in Excel, then I use an AHK script to make changes or do something with the Excel file while I am working in Excel. I could just use a VBA macro in Excel to do these things but that creates a macro dependency in the Excel file that freaks people out when I send the Excel file to someone and they get a security warning. I could also create the VBA macro in my Personal default Excel file which would allow me to run the script in any Excel file without adding a macro dependency which is what I do for macros that I use very often but I don't want to clutter up this default Excel file with lots of scripts that I only use rarely. I would brother use AHK for this less used scripts for Excel.

Also I am more comfortable coding in AHK than VBA so it I am doing something complex and tricky I use AHK over VBA and just have the AHK work on my ComObjActive Excel.

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
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

24 Feb 2017, 14:29

That makes great sense, FG. Thanks much for the detailed explanation — really appreciate it. Regards, Joe
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

24 Feb 2017, 15:04

JoeWinograd wrote:
FG wrote:ComObjActive connects you to an Excel process that is already running.
How does it know which Excel process to connect to?
If there is more than one active Excel object it can be a real pain to get the one you want. I have played with GetActiveObjects by lexikos but I was unable to get it to return all and only the Excel objects. I could not figure out what to use for the "Prefix".

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
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

24 Feb 2017, 15:46

FG wrote:I could not figure out what to use for the "Prefix".
Interesting. Would be nice to figure that out, although I don't see myself using more than one active Excel object.

On a different issue, since you have expertise in both AHK COM and VBA (which I presume carries over at least somewhat to VBS), I'm wondering if you can help me get the last piece of VBS translated to AHK at this thread:
https://autohotkey.com/boards/viewtopic ... 02#p134002
Just one statement left to translate — a VBS For Each loop. Thanks again, Joe
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

24 Feb 2017, 16:01

As was mentioned above, ComObjActive does not really know which process to connect to; at least not in the way you might expect. My vague understanding is that it gets the "active" object from the Running Object Table (ROT). If, for example, you have multiple instances of Excel running, ComObjActive will get the instance of Excel that is at the top of the ROT. An object's position on the ROT is not affected by which window is active; it has more to do with the order in which the objects were created. (I think)

So, imagine you want to have a hotkey that performs some action on the active cell in Excel. You want this hotkey to work on whatever Excel window you have active at the time. If you used ComObjActive you would not be guaranteed to get the correct object if you have more than one instance of the Excel process/application running. In this case you would have to do something more elaborate to ensure the correct object is retrieved. In the past, a popular solution to this problem has been a function called Excel_Get; a lot of versions of this function are floating around.
[Edit: Here's one example: Excel_Get.ahk]

Similarly and more recently, the GetActiveObjects function can be used. Earlier in this thread I gave an example of using GetActiveObjects to find a specific workbook. I've had more success with GetActiveObjects getting workbook objects, as opposed to Excel application objects. But, you can always get the application object from the workbook object with the Workbook.Application property.
[Edit: Also see: GetActiveWorkbook.ahk]
Last edited by kon on 10 May 2017, 16:36, edited 1 time in total.
User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: MS Office COM Basics

02 Mar 2017, 06:33

This page of mine has quite a few videos about using AutoHotkey with Excel.

In particular this post discusses Creating & Connecting to an Excel Object

And this one talks about the different "levels" of the Excel Object
Image
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

02 Mar 2017, 13:47

If you are interested in trying to sort out multiply Excel active objects then the below script using GetActiveObjects might help:

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
}
For me when I have one normal "Test" workbook open in Excel and run Lexikos code to show all active objects, I can see Excel objects like:

Code: Select all

C:\Program Files (x86)\Microsoft Office\Office14\Library\Analysis\ATPVBAEN.XLAM -- _Workbook
C:\Program Files (x86)\Microsoft Office\Office14\Library\Analysis\FUNCRES.XLAM -- _Workbook
C:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER\SOLVER.XLAM -- _Workbook
C:\Users\FG\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB -- _Workbook
C:\Users\FG\Desktop\Test\Test.xlsx -- _Workbook
I am not really interested in those plugin workbooks from Analysis and Solver. Those are Microsoft extensions you can turn on in Excel to give you additional special functions and abilities. I am also not generally interested in that Personal workbook. That is a hidden workbook that contains various default stuff.

So I wrote a GetExcel class that sorts out plugin stuff and does not return them and also allows me the option to show or not show hidden workbooks. I guess I could have made the class have an option to return the plugin stuff but I never foresee a situation where I would need to use COM with those Excel objects.

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
vincymol
Posts: 1
Joined: 24 Jul 2017, 02:50

Re: MS Office COM Basics

24 Jul 2017, 03:45

Thanks @kon
This is a great compilation to help understand COM
User avatar
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

27 Jul 2017, 10:38

Hi Kon,
Is there a way to disable macros when opening a Word file via COM? Thanks to your previous help, I'm using code like this to open a Word file read-only and then load the contents of it (without the Track Changes revisions) into an array:

Code: Select all

oWord:=ComObjCreate("Word.Application")
oDoc:=oWord.Documents.Open(InputFile,,1,,FilePassword)
oDoc.ShowRevisions:=0
InputDoc[1]:=oDoc.Content.Text
It works perfectly, but I'm concerned that it may run macros when opening the file, which I don't want it to do. I ran across this line elsewhere, but it doesn't work:

oDoc.DisableAutoMacros:=1

That gives Error 0x80020006 - Unknown name, so that obviously isn't the right variable to control the running of macros. Thanks for your insights on this. Regards, Joe
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

27 Jul 2017, 12:44

JoeWinograd wrote:Hi Kon,
Is there a way to disable macros when opening a Word file via COM? Thanks to your previous help, I'm using code like this to open a Word file read-only and then load the contents of it (without the Track Changes revisions) into an array:
oWord.EnableEvents := False before you open the document should stop any macros from triggering.
You might also look at oWord.AutomationSecurity := 3 ; msoAutomationSecurityForceDisable := 3 which should set the security mode to disabled macros.

Also I would assume that DisableAutoMacros is expecting an Application.DisableAutoMacros so oWord.DisableAutoMacros I believe this is a method and not a property (it is not well documented in the source I looked at). Also this would not prevent all macros from running just the special ones that are triggered by the open/close events. Lots of other events can trigger a macro.

I did not test any of this, just info from the MSDN.

FG
Last edited by FanaticGuru on 27 Jul 2017, 13:03, edited 1 time in total.
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
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

27 Jul 2017, 12:58

Hi FG,
Thanks for the idea, but oWord.EnableEvents:=False gives Error 0x80020006 - Unknown name. Also tried oDoc.EnableEvents:=False, but that gives the same error. Regards, Joe
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

27 Jul 2017, 13:13

JoeWinograd wrote:Hi FG,
Thanks for the idea, but oWord.EnableEvents:=False gives Error 0x80020006 - Unknown name. Also tried oDoc.EnableEvents:=False, but that gives the same error. Regards, Joe
I edited my post with more info while you were responding.

Sorry, I don't have time today to test a solution. There is also the possibility that the false needs to be -1 or even ComObject(0xB,-1). I believe kon explains this weirdness somewhere.

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
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

27 Jul 2017, 13:30

Yes, I'm familiar with that weirdness, discussed here:
https://autohotkey.com/boards/viewtopic ... 28#p111428

And kon mentions it here:
https://autohotkey.com/boards/viewtopic ... 40#p111440

I tried all of these:

oWord.EnableEvents:=0
oWord.EnableEvents:=-1
oWord.EnableEvents:=ComObject(0xB,0)
oWord.EnableEvents:=ComObject(0xB,-1)

They all throw the same error. Seems to be complaining about oWord.EnableEvents, not the value assigned to it. I'll look at your other (edited) ideas now. Thanks!
User avatar
JoeWinograd
Posts: 2166
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

12 Mar 2018, 22:28

Hi kon (and FanaticGuru and anyone else who can help with Outlook COM),

I posted a question about Outlook COM here:

https://autohotkey.com/boards/viewtopic.php?f=5&t=45505

It hasn't received any replies in several hours, so decided to post here, too. I'm hoping to get that script working soon. Thanks, Joe

Return to “Tutorials (v1)”

Who is online

Users browsing this forum: No registered users and 27 guests