Page 6 of 6

Re: MS Office COM Basics

Posted: 29 Apr 2018, 21:41
by 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?

Re: MS Office COM Basics

Posted: 30 Apr 2018, 15:07
by awel20
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

Re: MS Office COM Basics

Posted: 30 Apr 2018, 20:11
by RNDLDVL
Cheers mate, I doing it correctly after all just went out of bounds with my initial range.

Re: MS Office COM Basics

Posted: 04 May 2018, 19:20
by 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
Regards,
burque505

Re: MS Office COM Basics

Posted: 09 Jun 2018, 20:03
by 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 135 times
Spoiler
Regards,
burque505

Re: MS Office COM Basics

Posted: 22 Jun 2018, 02:32
by rickyc
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

Posted: 19 Jul 2018, 14:17
by 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?

Re: MS Office COM Basics

Posted: 19 Jul 2018, 15:29
by FanaticGuru
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

Re: MS Office COM Basics

Posted: 12 Feb 2019, 22:54
by 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

Re: MS Office COM Basics

Posted: 09 Mar 2019, 20:28
by 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

Re: MS Office COM Basics

Posted: 21 May 2019, 03:35
by Mdmanik
Wow, thank you so much for this extensive and detailed guide. I appreciate it.

Re: MS Office COM Basics

Posted: 17 Jan 2020, 15:03
by 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