MS Office COM Basics

Helpful script writing tricks and HowTo's
Posts: 15
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

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: ... 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

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?
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: MS Office COM Basics

30 Apr 2018, 15:07

RNDLDVL wrote:
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

ws := ComObjActive("Excel.Application").Worksheets(1)

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

; Error: Unable to get the Subtotal property of the Range class
ws.Range("B1:F7").Subtotal(1, -4157, safeArr)

; OK
ws.Range("B1:G7").Subtotal(1, -4157, safeArr)

Posts: 15
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

30 Apr 2018, 20:11

Cheers mate, I doing it correctly after all just went out of bounds with my initial range.
Posts: 1445
Joined: 22 Jan 2017, 19:37

Re: MS Office COM Basics

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

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
SetTitleMatchMode, 2

wp := ComObjCreate("WordPro.Application")
WinGet, pid, PID, ahk_exe wordpro.exe
wp.Visible := True
WinMaximize, %pid%
WinShow, %pid%
wpdoc := wp.ActiveDocument
docpath := wpdoc.Path
docname := wpdoc.Name
wpdocText := wp.Text

wp.Type("This is the first paragraph entered from AutoHotkey via COM automation.[Enter]")
;wp.Type("COM automation.[Enter]") ; Although this is on another line,
									; only [Enter] starts a paragraph.
									; Experiment.

wp.Type("The second paragraph will be changed in its ")
WPDocText.Font.Name := "Arial"
WPDocText.Font.Bold := True
WPDocText.Font.Italic := True
WPDocText.Font.Size := 15

;Uncomment line below to print
;wp.PrintOut(1, 1, 1, True)


Msgbox Created %docpath%\%docname%

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:
Posts: 1445
Joined: 22 Jan 2017, 19:37

Re: MS Office COM Basics

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.
(3.44 KiB) Downloaded 235 times
Posts: 5
Joined: 04 Jun 2018, 01:41

Re: MS Office COM Basics

22 Jun 2018, 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

19 Jul 2018, 14:17

When passing text to bookmarks in Microsoft Word, I am unable to send line feeds using `n. Is there any way to send text with carriage returns or line feeds?
Posts: 1495
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

19 Jul 2018, 15:29

Sollermun wrote:When passing text to bookmarks in Microsoft Word, I am unable to send line feeds using `n. Is there any way to send text with carriage returns or line feeds?
Chr(11) is newline, Chr(13) is new paragraph.

Something like this:
.InsertAfter("Stuff goes here" Chr(11) "then start new line" Chr(13) "Now do a new paragraph")

Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
Posts: 561
Joined: 08 May 2018, 10:20

Re: MS Office COM Basics

12 Feb 2019, 22:54

Thank you very much this is precious info condensed in one place.

It was able to teach me from complete mysteries to beginner/practical understanding.
The VB macro recorder is a genious idea. Thanks
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: MS Office COM Basics

09 Mar 2019, 20:28

Very, very and very nice post !!!

If you had some leisure time, add a little bit about 'Subscription model of MS Office (365 something)'.
I do not have any experience about it but some people are using it (and reported strange problems).

Posts: 1
Joined: 21 May 2019, 03:27

Re: MS Office COM Basics

21 May 2019, 03:35

Wow, thank you so much for this extensive and detailed guide. I appreciate it.
User avatar
Posts: 1648
Joined: 10 Feb 2014, 20:00
Location: U.S. Central Time Zone

Re: MS Office COM Basics

17 Jan 2020, 15:03

Hi kon,
Hard to believe that it's been more than three years since our last exchange here...amazing how time flies! I posted this question earlier today at a tmplinshi thread, but in case he's out of pocket, decided to post here, too.

I'm trying to convert the following Excel VBA to AHK:

Code: Select all

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
col = ActiveCell.Column
Range(ActiveCell, Cells(lastRow, col)).Select
n = Application.CountA(Selection)       'Non-Blank cells in the selection
I converted the first three VBA lines as follows:

Code: Select all

xlCellTypeLastCell:=11 ; enumeration -
A MsgBox shows that lastRow and col are correct. Also, the range of cells is properly selected in Excel. However, I'm stuck on the last VBA line, as it's not clear to me what Selection needs to be in AHK for the CountA method. I thought that it would be the value that I assigned to it in the last line posted above, but that doesn't work — n always come back as 1 with this:

Code: Select all

Thanks for your help. Regards, Joe

Edit on 18-Jan-2020: For others following this thread, the answer to my question is here:
Regards, Joe

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 5 guests