MS Office COM Basics

Helpful script writing tricks and HowTo's
RNDLDVL
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:
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

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

Re: MS Office COM Basics

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

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)

ExitApp
RNDLDVL
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.
burque505
Posts: 1731
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%
wp.NewDocument("AutomateMe.lwp",A_ScriptDir,"default.mwp")
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 ")
wp.Type("appearance.")
wp.SelectParagraph
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)

WPDoc.Save

Msgbox Created %docpath%\%docname%

WPDoc.Close
wp.Quit
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
burque505
Posts: 1731
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.
WordPerfect.7z
(3.44 KiB) Downloaded 452 times
Spoiler
Regards,
burque505
rickyc
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:
Sollermun

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?
User avatar
FanaticGuru
Posts: 1905
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")

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
DRocks
Posts: 565
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
IMEime
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: MS Office COM Basics

09 Mar 2019, 20:28

Thanks
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).

Regards
Mdmanik
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
JoeWinograd
Posts: 2165
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 - https://docs.microsoft.com/en-us/office/vba/api/excel.xlcelltype
xl:=ComObjActive("Excel.Application")
lastRow:=xl.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
col:=xl.ActiveCell.Column
ActiveCellAddress:=xl.ActiveCell.Address[0,0]
ActiveRange:=xl.Cells(lastRow,col)
Selection:=xl.Range(ActiveCellAddress,ActiveRange).Select
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

n:=Floor(xl.CountA(Selection))
Thanks for your help. Regards, Joe

Edit on 18-Jan-2020: For others following this thread, the answer to my question is here:
https://www.autohotkey.com/boards/viewtopic.php?f=76&t=71711&p=310222#p310222
Regards, Joe
tpitera
Posts: 31
Joined: 27 Oct 2020, 15:56

Re: MS Office COM Basics

26 Apr 2021, 15:00

@kon
CellA1 := xlApp.Cells(1, 1) ; Store a reference to cell A1

; Find the last cell in Column 'A' that is not blank.
; Start at the last cell in Column 'A' and look upwards for a non-blank cell
LastCell := xlApp.Cells(xlApp.Rows.Count, 1).End(-4162) ; xlUp = -4162
Not sure why but i copy and pasted your tutorial and this part did not work for me

Also how do you know how to use .Cells and .End? is there a list of .functions that can be used in excel and their functionality.
I been trying to read through every autohotkey forum and cant seem to find it

When i tried this, it did work but .Cells still doesnt work but getting A1 contents works with the other calls

Code: Select all

xlApp := ComObjCreate("Excel.Application")                            ; Create an Excel application
xlApp.Visible := true      ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath)                                ; Open the workbook
CellA1 := x1App.Cells(1,1)
MsgBox % "Cell A1 = " . CellA1
if (CellA1 = "")
{
	oWorkbook := ComObjActive("Excel.application")  ;retrieve active workbook
	Cell1A := oWorkbook.Range("A1").Value
}
CellA1b := x1App.range("a3").text
MsgBox % "Cell A1 new = " . Cell1A . "`nCell A1c = " . oWorkbook.Sheets(1).Range("A2").Text 
Any advice you could provide would be very much appreciated
User avatar
Blackholyman
Posts: 1293
Joined: 29 Sep 2013, 22:57
Location: Denmark
Contact:

Re: MS Office COM Basics

27 Apr 2021, 03:28

tpitera wrote:
26 Apr 2021, 15:00
When i tried this, it did work but .Cells still doesnt work but getting A1 contents works with the other calls

Code: Select all

xlApp := ComObjCreate("Excel.Application")                            ; Create an Excel application
xlApp.Visible := true      ; Optional. Just remember to close Excel at the end if it is not visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath)                                ; Open the workbook
CellA1 := x1App.Cells(1,1)
MsgBox % "Cell A1 = " . CellA1
if (CellA1 = "")
{
	oWorkbook := ComObjActive("Excel.application")  ;retrieve active workbook
	Cell1A := oWorkbook.Range("A1").Value
}
CellA1b := x1App.range("a3").text
MsgBox % "Cell A1 new = " . Cell1A . "`nCell A1c = " . oWorkbook.Sheets(1).Range("A2").Text
Any advice you could provide would be very much appreciated
in your example you have a typo, x(1)App => x(L)App

Fixing this will most likely make it work a little better :)
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

27 Apr 2021, 12:34

tpitera wrote:
26 Apr 2021, 15:00
Also how do you know how to use .Cells and .End? is there a list of .functions that can be used in excel and their functionality.
I been trying to read through every autohotkey forum and cant seem to find it

Here is a link to documentation on the Excel object model.

https://docs.microsoft.com/en-us/office/vba/api/overview/excel

What you refer to as .functions are called methods and properties. Methods do something while properties just contain a value that can you can get and set. The list of both is extensive.

You can often just google something like msdn cells and usually get a good link to start. Just include msdn and the method or property you are looking for. MSDN stands for Microsoft Developer Network and is a commonly used abbreviation for the database that contains information for developers.

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
tpitera
Posts: 31
Joined: 27 Oct 2020, 15:56

Re: MS Office COM Basics

27 Apr 2021, 14:19

@Blackholyman Ok initially i didnt think there was a problem but i restarted from the beginning with a new var and it worked, but the Last Cell is still not working.
Also, I am looking for a way to see all properties listed under the object XL and WB1 to see what is possible what is not, has anyone ever tried this? because Cells didnt work under WB1, had to be XL

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true 
WB1 := XL.Workbooks.Open(source)
For key, value in XL
	MsgBox %key% = %value%

Value := WB1.ActiveSheet.Range("A1").Text 
MsgBox % "Value = " . Value
Value2 := XL.Worksheets(1).Cells(1, 1).Value ;works
MsgBox % "Value2 = " . Value2
XL.Worksheets(1).Cells(4, 2).Value := "BORREGO"
WB1.ActiveSheet.Range("B5").Value := "ROCK CREEK"
LastCell := XL.Worksheets(1).Cells(XL.Worksheets(1).Rows.Count, 1).End(-4162)     ;xlUp = -4162, doesnt work
MsgBox % "Last Cell= " . LastCell
[\code]

[mention]FantasticGuru[/mention] Yes i did find this source, unfortunately I dont see .End, it doesnt tell me why something isnt working.. is there a way I can troubleshoot better?
for the future, how would i go about fixing a problem like last cell
Is there a document that references (or can i put in my code a way to find)
ComObjCreate("Excel.Application") methods and properties that work
ComObjCreate("Excel.Application").Workbooks.Open(source).ActiveSheet  methods and properties that work
ComObjCreate("Excel.Application").Worksheets(1) methods and properties that work
Idk if its just because I am new to this specifically but there seems to be a large variation of possibilities before the VBA portion
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: MS Office COM Basics

29 Apr 2021, 13:22

tpitera wrote:
27 Apr 2021, 14:19
Also, I am looking for a way to see all properties listed under the object XL and WB1 to see what is possible what is not, has anyone ever tried this? because Cells didnt work under WB1, had to be XL

...

@FantasticGuru Yes i did find this source, unfortunately I dont see .End, it doesnt tell me why something isnt working.. is there a way I can troubleshoot better?
for the future, how would i go about fixing a problem like last cell
Is there a document that references (or can i put in my code a way to find)
ComObjCreate("Excel.Application") methods and properties that work
ComObjCreate("Excel.Application").Workbooks.Open(source).ActiveSheet methods and properties that work
ComObjCreate("Excel.Application").Worksheets(1) methods and properties that work
Idk if its just because I am new to this specifically but there seems to be a large variation of possibilities before the VBA portion

It is all in the MSDN link provided above, you just use the tree to the left side to navigate to the object you want.

For example, here are all the Events, Methods, and Properties of the Application object: https://docs.microsoft.com/en-us/office/vba/api/excel.application(object)
That is everything that can go after the XL in your example.

All the same for the Workbook object: https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks
That is everything that can go after the WB1 in your example.

And the Worksheet object: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet

Here is the End object: https://docs.microsoft.com/en-us/office/vba/api/excel.range.end

You can see that End is a property of a Range object which in turn is a property of a Worksheet object. An Object model is like a giant tree. Each entry in the MSDN tells you the objects below it and the object that is directly above it in the tree. Typically you find an object that does what you need then you work backwards to find how to get a handle to that object.

It is not like a secret code where people memorize the combinations of what can go before and after each . in an object reference. Being good at working with COM objects is mostly about being good at referencing the MSDN documentation of the COM object.

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

Return to “Tutorials (v1)”

Who is online

Users browsing this forum: No registered users and 30 guests