MS Office COM Basics

Post a reply

Confirmation code
Enter the code exactly as it appears. All letters are case insensitive.
Smilies
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :| :mrgreen: :geek: :ugeek: :arrow: :angel: :clap: :crazy: :eh: :lolno: :problem: :shh: :shifty: :sick: :silent: :think: :thumbup: :thumbdown: :salute: :wave: :wtf: :yawn: :facepalm: :bravo: :dance: :beard: :morebeard: :xmas: :HeHe: :trollface: :cookie: :rainbow: :monkeysee: :monkeysay: :happybday: :headwall: :offtopic: :superhappy: :terms: :beer:
View more smilies

BBCode is ON
[img] is OFF
[flash] is OFF
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: MS Office COM Basics

Re: MS Office COM Basics

Post by rickyc » Today, 02:32

You have provided such a great information!!..I'm much obliged to you for giving this data to us. :thumbup:

Re: MS Office COM Basics

Post by burque505 » 09 Jun 2018, 20:03

Again marginally off-topic: COM and WordPerfect.
WP is actually very automation-friendly - especially nice is the KeyType() command, which is really fast.
Being able to insert files into documents easily is nice too.
Unfortunately the Office Compatibility Pack for Corel is not available at the moment, but Corel claims it will be out in June 2018.
EDIT: You can find the Compatibility Pack from unofficial sources. I just found it on FileHippo. A preliminary conversion to .docx with this script worked just fine.

The script below with a couple of files referenced in the script are in a .7z archive below, if you want to take it for a spin.
WordPerfect.7z
(3.44 KiB) Downloaded 8 times


Spoiler

Regards,
burque505

Re: MS Office COM Basics

Post by burque505 » 04 May 2018, 19:20

Marginally off-topic, but it is COM, and the programs involved are office programs (even if not Office programs :)):
In an excursion back into the dinosaur years, I was reminded that IBM made its (pretty cool) LotusSmartSuite99 available for free.
The Lotus documentation itself available wasn't very good about using COM for it, but I found some while searching for a good REXX implementation.
I settled on Open Object REXX. To my delight, I found some OLE samples right under the prog directory, some of them for WordPro.
Took about ten minutes to port to AHK.

Code: [Select all] [Expand]GeSHi © Codebox Plus


REXX can do cool things. Here's the original REXX sample so you can see how easy REXX COM stuff should be to port to AHK:
Spoiler


Regards,
burque505

Re: MS Office COM Basics

Post by RNDLDVL » 30 Apr 2018, 20:11

Cheers mate, I doing it correctly after all just went out of bounds with my initial range.

Re: MS Office COM Basics

Post by awel20 » 30 Apr 2018, 15:07

RNDLDVL wrote:
Spoiler
As far as I can tell, it could be because safeArr[1] := 6 refers to column 'G' which is outside the Range Range("B1:F4649"). The offsets seem to be from the left side of the range: B=1, C=2, D=3, E=4, F=5, G=6.
Also, according to google:
- range can't be in a table
- column headings are required for all columns

Code: [Select all] [Expand]GeSHi © Codebox Plus

Re: MS Office COM Basics

Post by RNDLDVL » 29 Apr 2018, 21:41

Hello, Anyone might have an idea how to properly set the TotalList parameter of the Range.Subtotal function.

Here is the msdn page for the function:
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

The page describes the parameter as a Variant type of "An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example."

Here's what I came up with:

Code: [Select all]GeSHi © Codebox Plus

safeArr := ComObjArray(12, 2)
safeArr[0] := 5
safeArr[1] := 6

ws.Range("L2:M3").Value := safeArr
ws.Range("B1:F4649").Subtotal(1, -4157, safeArr)


Assigning the array works fine which means it really is of variant type, however the Subtotal command won't accept the array as a parameter. Anyone might have an idea to make this work?

Re: MS Office COM Basics

Post by JoeWinograd » 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

Re: MS Office COM Basics

Post by JoeWinograd » 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!

Re: MS Office COM Basics

Post by FanaticGuru » 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

Re: MS Office COM Basics

Post by JoeWinograd » 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

Re: MS Office COM Basics

Post by FanaticGuru » 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

Re: MS Office COM Basics

Post by JoeWinograd » 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]GeSHi © Codebox Plus

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

Re: MS Office COM Basics

Post by vincymol » 24 Jul 2017, 03:45

Thanks @kon
This is a great compilation to help understand COM

Re: MS Office COM Basics

Post by FanaticGuru » 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] [Expand]GeSHi © Codebox Plus

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]GeSHi © Codebox Plus

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

Re: MS Office COM Basics

Post by Joe Glines » 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

Re: MS Office COM Basics

Post by kon » 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]

Re: MS Office COM Basics

Post by JoeWinograd » 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

Re: MS Office COM Basics

Post by FanaticGuru » 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

Re: MS Office COM Basics

Post by JoeWinograd » 24 Feb 2017, 14:29

That makes great sense, FG. Thanks much for the detailed explanation — really appreciate it. Regards, Joe

Re: MS Office COM Basics

Post by FanaticGuru » 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

Top