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."
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
Hello, Anyone might have an idea how to properly set the TotalList parameter of the Range.Subtotal function.
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."
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
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.
#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:
/*----------------------------------------------------------------------------*/
/* */
/* 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
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.
#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
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.
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:
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
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: