MS Office COM Basics

[RNDLDVL]

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]

Spoiler

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?

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]

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

[burque505]

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

Code: Select all

/*----------------------------------------------------------------------------*/
/*                                                                            */
/* Copyright (c) 1995, 2004 IBM Corporation. All rights reserved.             */
/* Copyright (c) 2005-2014 Rexx Language Association. All rights reserved.    */
/*                                                                            */
/* This program and the accompanying materials are made available under       */
/* the terms of the Common Public License v1.0 which accompanies this         */
/* distribution. A copy is also available at the following address:           */
/* http://www.oorexx.org/license.html                          */
/*                                                                            */
/* Redistribution and use in source and binary forms, with or                 */
/* without modification, are permitted provided that the following            */
/* conditions are met:                                                        */
/*                                                                            */
/* Redistributions of source code must retain the above copyright             */
/* notice, this list of conditions and the following disclaimer.              */
/* Redistributions in binary form must reproduce the above copyright          */
/* notice, this list of conditions and the following disclaimer in            */
/* the documentation and/or other materials provided with the distribution.   */
/*                                                                            */
/* Neither the name of Rexx Language Association nor the names                */
/* of its contributors may be used to endorse or promote products             */
/* derived from this software without specific prior written permission.      */
/*                                                                            */
/* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS        */
/* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT          */
/* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS          */
/* FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT   */
/* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,      */
/* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED   */
/* TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA,        */
/* OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY     */
/* OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING    */
/* NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS         */
/* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.               */
/*                                                                            */
/*----------------------------------------------------------------------------*/
/**********************************************************************/
/*                                                                    */
/* SAMP05.REX: OLE Automation with Object REXX - Sample 5             */
/*                                                                    */
/* Create a new document in WordPro 97, enter some text with          */
/* different attributes and finally save and print the document.      */
/*                                                                    */
/* Since no check is done do ensure the new document does not already */
/* exist you will get a popup message from WordPro asking to          */
/* overwrite an already existing document when this sample is run     */
/* multiple times.                                                    */
/*                                                                    */
/**********************************************************************/

WordProApp = .OLEObject~New("WordPro.Application")
WordProApp~NewDocument("AutomatedDocument.lwp","","default.mwp")
WPDoc = WordProApp~ActiveDocument
WPDocText = WordProApp~Text

WordProApp~Type("This is the first paragraph entered from REXX via ")
WordProApp~Type("the OLE automation classes.[Enter]")

WordProApp~Type("The second paragraph will be changed in its ")
WordProApp~Type("appearance.")
WordProApp~SelectParagraph
WPDocText~Font~Name = "Arial"
WPDocText~Font~Bold = .True
WPDocText~Font~Italic = .True
WPDocText~Font~Size = 15

WordProApp~Type("[End][Enter][End]Document created at:" Time("N") "on" Date("N"))

WPDoc~Save

/* if you want this document printed, comment in the next line */
--WordProApp~PrintOut(1, 1, 1, .True)

say "Created" WPDoc~Path"\"WPDoc~Name

WPDoc~Close
WordProApp~Quit

Regards,
burque505

[burque505]

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 654 times

Spoiler

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.
#Persistent
SetTitleMatchMode, 2
myDir := A_ScriptDir
myEuro := "€"
myString := "Thank you. This lesson cost " . myEuro . " 100. Pay at the door."
;msgbox %myString%
myText:=
(
"Lorem ipsum dolor sit amet, consectetur adipisicing elit. Soybean gram endive greens summer. Seakale bitterleaf, other leone courgette cauliflower. Corn greens nori many those spring beet celery quandong purslane sprouts! Okra brussels, fennel horseradish sprout zucchini, quandong water. 

Root water onion coriander nuts, lotus spring water, celtuce rock salsify! Jícama other chicory bean many napa pumpkin summer gourd corn leone? Carrot plantain soko, pepper broccoli. Maize napa bologi tomato salsify greens, chard peanut ricebean turnip, daikon leek? Garbanzo kohlrabi much nuts spinach, plantain! 

Prairie cauliflower asparagus desert cabbage earthnut cauliflower salsify? Nuts aubergine much sprouts kohlrabi. Carrot ricebean fennel okra radicchio groundnut, turnip, purslane zucchini! Artichoke onion tatsoi bitterleaf beetroot nuts peanut watercress horseradish gumbo! Shallot cabbage coriander sierra courgette seed. Gumbo horseradish garbanzo daikon bell. Kakadu, seakale watercress winter garlic, a sierra together grape azuki avocado! Catsear taro chicory collard avocado lentil! Pepper quandong other!"
)

wp := ComObjCreate("WordPerfect.PerfectScript")
wpHwnd := WinExist("A")
wp.WPActivate()
wp.AppMaximize()
wp.FileNew()
wp.KeyType("This is my first text. It has one line now.")
wp.HardReturn()
wp.PosDocBottom()
wp.KeyType(myText)
wp.PosDocBottom()
wp.FileInsert(A_ScriptDir . "\lorem.wpd")
wp.HardReturn()
wp.PosDocBottom()
wp.HardReturn()
wp.HardReturn()
wp.KeyType(myString)
wp.FileSaveAsMSWord() ; At the moment the File Compatibility Pack is not available for docx.
						; A solution is expected this month (June 2018).
wp.ExitWordPerfect() ; You'll get a save dialog, so it won't bail right away.
wp =

ExitApp

Regards,
burque505

[rickyc]

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

[Sollermun]

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?

[FanaticGuru]

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

[DRocks]

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]

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]

Wow, thank you so much for this extensive and detailed guide. I appreciate it.

[JoeWinograd]

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]

@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

[Blackholyman]

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

[FanaticGuru]

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

[tpitera]

@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

[FanaticGuru]

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