MS Office COM Basics

Helpful script writing tricks and HowTo's
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

13 Nov 2016, 11:03

How do I properly detect if the excel workbooks I want my script to interact with are already open in the background or foreground? Since they don't have fixed names. I use this to specify which worksbooks are which but I'm not sure how to check if they are open.

kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

13 Nov 2016, 15:10

RNDLDVL wrote:How do I properly detect if the excel workbooks I want my script to interact with are already open in the background or foreground? Since they don't have fixed names. I use this to specify which worksbooks are which but I'm not sure how to check if they are open.

It is easiest if you can avoid having the user open workbooks. If it is just your script that will be opening and closing workbooks, have your script maintain a list of the currently open workbooks. On the other hand...

So given a workbook path you want to check if that workbook is open? :) Here's some ways to do it.

To simply check if the workbook is open, check if you can open the file with FileOpen.

Code: [Select all] [Download] GeSHi © Codebox Plus

if FileOpen("C:\Test\Book1.xlsx", "rw")  ; FileOpen fails if the file is already open
MsgBox, File is not open.
else
MsgBox, File is open.

To get the workbook object it is less complicated if you only have one Excel Application open (or if you know which Application contains the workbook and you already have a reference to that application). An Excel Application object has the Workbooks collection. This collection contains all of the open workbooks. So you can loop through the Workbooks collection and check if any of the workbooks match the one you are looking for.
This example checks if "C:\Test\Book1.xlsx" is open.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus


If more than one Excel Application is open it becomes more complicated to determine if a workbook is open. You need to check in each application. One way to do that is to use GetActiveObjects().
This example checks if "C:\Test\Book2.xlsx" is open.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus


If you need to split a file path into separate parts to compare with obj.Path and obj.Name, use the SplitPath command.
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

14 Nov 2016, 09:04

kon wrote:If more than one Excel Application is open it becomes more complicated to determine if a workbook is open. You need to check in each application. One way to do that is to use GetActiveObjects().
This example checks if "C:\Test\Book2.xlsx" is open.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus


If you need to split a file path into separate parts to compare with obj.Path and obj.Name, use the SplitPath command.


Thanks, this is exactly what I was looking for. I was messing around earlier with GetActiveObjects(), but I wasn't producing results, your example really showed me what I was doing wrong. Thanks again.
JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Nov 2016, 13:28

Hi kon,

Thanks again for your excellent tutorial! I have found it to be extremely helpful in learning how to use COM in AHK. But I've run into an issue that I can't solve, so I'm hoping you'll have the answer.

I'm using COM to read the entire contents of a Word file (DOC/DOCX) into a variable. Here's the code snippet:

Code: [Select all] [Download] GeSHi © Codebox Plus

try oDoc:=ComObjGet(InputFile)
catch
{
MsgBox,4112,Fatal Error,Error trying to access input document:`n%InputFile%
ExitApp
}


I don't want it to show the Word window when it does the ComObjGet, and that's exactly how it works when a file is not password protected. But when a file is password protected, it displays a blank Word document when it shows the Password dialog box ("Enter password to open file"). Is there any way to stop that? I'd like it to show just the Password dialog box, not the blank Word document behind it. Thanks, Joe
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Nov 2016, 16:39

I found one page that may help: Skipping Password-Protected Documents in a Batch Process

You will need to use ComObjCreate instead of ComObjGet since this requires access to the Application object prior to opening the file.

Maybe something like this:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Nov 2016, 18:43

Hi kon,
Your code works perfectly! Thanks very much! I've never used throw Exception before — can you give me a brief explanation of why it's there? I checked the doc on it (both Throw and Exception), but it's eluding me. Thanks again, Joe
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Nov 2016, 18:55

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus


The Exception function creates an object similar to the object stored in e in the above code snippet.
JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Nov 2016, 19:51

Thanks for the explanation — very helpful!
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Nov 2016, 21:34

Actually, no need for the Exception function (duh, now that I think about it):
throw e
JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Nov 2016, 23:02

Don't even think I'll use the throw there. That path runs only when Word can't open the file and it's for a reason other than the password being incorrect. At that point, I'm fine with exiting the script with a "Fatal Error - cannot open file" dialog. I don't even know what would cause that to happen — Word not installed? User doesn't have access permission?
JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

22 Nov 2016, 10:44

Hi kon,
The script now does exactly what I want, but there's one problem with the new version — it leaves a WINWORD.EXE *32 process. This must due to the ComObjCreate("Word.Application") call, because the version with just the ComObjGet(InputFile) call does not leave around such a process. I thought that the solution might be to do an ObjRelease(wdApp) right after the oDoc.close(0), but that did not end the WINWORD.EXE *32 process. How would the script end that process? Thanks, Joe
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

22 Nov 2016, 11:13

wdApp.Quit

If you have made changes to the workbook(s) you may have to save them (like in the example), or set the DisplayAlerts property to False before quitting.
JoeWinograd
Posts: 763
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

22 Nov 2016, 11:55

wdApp.Quit did the trick! Btw, I'm not modifying the Word file — just doing this:

Code: [Select all] [Download] GeSHi © Codebox Plus

oDoc.ShowRevisions:=0
InputDoc[1]:=oDoc.Content.Text
oDoc.close(0)

Now I'm following that up with this:


Seems that setting the DisplayAlerts property is not needed, but thanks for the link — good to have in my bag of tricks.

All is working very well now! Thanks again for your help. Regards, Joe
SmokeyTBear
Posts: 4
Joined: 23 Mar 2015, 18:06

Re: MS Office COM Basics

29 Nov 2016, 07:56

I wrote the code below to parse a system generated email, create a chart, and email the chart and formatted data to recipient, Got it to the point(sloppy im sure) that the chart gets created, the only things im missing is adding "warning" to the legend field in the pivot table, and changing Axis(catagories) from "time" to "hours" this will group the warnings by hours instead of individual time stamp. If I do that manually I seem to get the result I want. After that, I would just want to delete the chart title, and then assign the chart to an object/var to embed, or get it onto the clipboard.

Thank you for any help.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus




forum posts I am working from are

http://autohotkey.com/board/topic/14954 ... -chart-com

and

http://autohotkey.com/board/topic/12571 ... ivot-table

End result I am looking for to embed into an outlook email:

http://imgur.com/a/6baLe

Sample input:

http://p.ahkscript.org/?p=a0ceb3b1

Thank you for any help.
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

29 Nov 2016, 09:46

How does one translate the following Array() parameters to ahk syntax? Eg.

Code: [Select all] [Download] GeSHi © Codebox Plus

Criteria1:=Array("5130", "5132", "5134")
Criteria2:=Array(2, "8/1/2016")
Criteria1:=Array("=")


I tried declaring and populating an AHK arrays but it seems that the excel methods won't take it.
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

29 Nov 2016, 17:04

@SmokeyTBear
I'm not sure what's left to do... But, I did simplify some things.
There were two instances where you used = when it should have been :=.
It might help if you include an example with more warnings; your example data only contains 2.
Also, you tried to use xlColumnField but that variable was blank. I made that var into a static variable and assigned it a value. See section 5.1 "Constants" of this tutorial.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

For future forum users' reference, this question was also asked here: https://autohotkey.com/boards/viewtopic.php?f=5&t=24991

Edit:
SmokeyTBear wrote:the only things im missing is adding "warning" to the legend field in the pivot table, and changing Axis(catagories) from "time" to "hours" this will group the warnings by hours instead of individual time stamp. If I do that manually I seem to get the result I want.
What are the steps to do this manually?
If you record yourself changing it manually with the Excel macro recorder, what VBA code does it produce?


@RNDLDVL
Maybe a safe array? There's an example of a safe array in the code directly above^ (look for the comment, "; Create a safe array and copy data into it.").
Can you give an example of the MS Office command you are trying to use?
RNDLDVL
Posts: 10
Joined: 04 Oct 2015, 10:48

Re: MS Office COM Basics

30 Nov 2016, 02:34

Yeah, it's seems to be working now for the parameters I've specified earlier. However, I can't seem to get the proper data type for this parameter:

Code: [Select all] [Download] GeSHi © Codebox Plus

Key:=Range(A2:A14050)


Here's the whole line for referrence:

Code: [Select all] [Download] GeSHi © Codebox Plus

xSF := wbs.Worksheets(1).ListObjects(1).Sort.SortFields
xSF.Clear
xSF.Add(Key:=Range("A2:A148023"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
xSF.Add(Key:=Range("H2:H148023"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
xSF.Add(Key:=Range("C2:C148023"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
;xSort := wbs.Worksheets("SOURCE").ListObjects(1).Sort
; xSort.Header := xlYes
; xSort.MatchCase := False
;xSort.Orientation := xlTopToBottom
; xSort.SortMethod := xlPinYin
; xSort.Apply)


I tried out declaring the variables like these:

Code: [Select all] [Download] GeSHi © Codebox Plus

tStr := "A2:A148023"
tSA := ComObjType(VT_VARIANT:=12, tStr) ;Range("A2:A148023")
tSB := ComObjType(VT_VARIANT:=12, "H2:H148023") ;Range("H2:H148023")
tSC := ComObjType(VT_VARIANT:=12, "C2:C148023") ;Range("C2:C148023")


I tried both VT_BSTR and VT_Variant, I keep ending up with a type mismatch error. And if you have an idea on a better way on accomplishing this, I'm open for suggestions.

Edit: Nevermind, I figured it out by doing this to reference the range.

Code: [Select all] [Download] GeSHi © Codebox Plus

xSF.Add(wbs.ActiveSheet.Range("A2:A148023"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
Last edited by RNDLDVL on 30 Nov 2016, 02:59, edited 1 time in total.
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

30 Nov 2016, 02:56

In VBA, the Application object is implicit.
Range("C2:C148023") is the same as Application.Range("C2:C148023").

So in AHK, try Var.Range("C2:C148023"). (Where Var is a variable containing a reference to an application object.)
SmokeyTBear
Posts: 4
Joined: 23 Mar 2015, 18:06

Re: MS Office COM Basics

02 Dec 2016, 10:41

Kon: Thank you so much! I have been stuck on this longer then I care to admit.

The VBA output of what I need to do to the chart still is:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus




Basically trying to split it up based on the hour, instead of total timestamp, so I can group warnings throughout the day and then I want to delete the chart title.


After that is done, what would be the best way to add the chart, and then the data to my COM Outlook email?
kon
Posts: 1760
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

02 Dec 2016, 12:55

Spoiler
(Correction: See my second edit below.)

In the meantime, try this. I incorporated the VBA code from the macro you recorded.
It also deletes the chart title now, and shows one way to copy the chart into an email.

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus


Edit: Instead of making the chart from scratch, have you considered using a template? You could create the chart and format it, then save the workbook as a template (.xltx). Then all your script would need to do is populate the data.

Edit2: I didn't see part of the VBA code you posted. I've updated the code above to include it. But, PivotField.AutoGroup was introduced in Office 2016 so I can't test it. Also, the PivotField named "Minutes" doesn't seem to exist? (Maybe that is also related to PivotField.AutoGroup.)

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 10 guests