MS Office COM Basics

Helpful script writing tricks and HowTo's
RNDLDVL
Posts: 12
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] [Download] 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?
awel20
Posts: 17
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] [Expand] [Download] GeSHi © Codebox Plus

RNDLDVL
Posts: 12
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: 502
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] [Expand] [Download] 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
burque505
Posts: 502
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 17 times


Spoiler

Regards,
burque505
rickyc
Posts: 6
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?
FanaticGuru
Posts: 1124
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

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 7 guests