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

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.

Code: Select all

Workbook := ComObjGet(path)
kon
Posts: 1756
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.

Code: Select all

Workbook := ComObjGet(path)
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

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

; **If only one Excel application is open**
xlApp := ComObjActive("Excel.Application")  ; Get the "Active" Excel application object
MyWbk := ""                                 ; Clear the variable
for Wbk, in xlApp.Workbooks                 ; For each workbook in the application's workbooks collection...
{
    ;~ MsgBox, % Wbk.Name "`n" Wbk.Path                     ; Show the name and path of this workbook
    if (Wbk.Path = "C:\Test" && Wbk.Name = "Book1.xlsx")    ; If this workbook's path and name match...
    {
        MyWbk := Wbk                        ; Save a reference to this workbook
        break                               ; Break the for-loop
    }
}


; Here we test the reults
if MyWbk                                    ; If MyWbk is not blank show the name
    MsgBox, % MyWbk.Name " was found."
else
    MsgBox, % "The specified workbook was not found."
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

; Show a list of all active objects
; This is here only to show you the full list. The next step shows how to get
; a specific item from this list.
;~ list := ""
;~ for name, obj in GetActiveObjects()
    ;~ list .= name " -- " ComObjType(obj, "Name") "`n"
;~ MsgBox %list%

MyWbk := ""  ; Clear the variable
for name, obj in GetActiveObjects()
{
    if (ComObjType(obj, "Name") = "_Workbook")                  ; If this object is a workbook...
    {
        ;~ MsgBox, % obj.Path "`n" obj.Name                     ; Show the name and path of this workbook
        if (obj.Path = "C:\Test" && obj.Name = "Book2.xlsx")    ; If this workbook's path and name match...
        {
            MyWbk := obj                        ; Save a reference to this workbook
            break                               ; Break the for-loop
        }
    }
}


; Here we test the reults
if MyWbk                                        ; If MyWbk is not blank show the name
    MsgBox, % MyWbk.Name " was found."
else
    MsgBox, % "The specified workbook was not found."



; GetActiveObjects v1.0 by Lexikos
; http://ahkscript.org/boards/viewtopic.php?f=6&t=6494

;<Paste the GetActiveObjects function definition here>
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: 12
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

; Show a list of all active objects
; This is here only to show you the full list. The next step shows how to get
; a specific item from this list.
;~ list := ""
;~ for name, obj in GetActiveObjects()
    ;~ list .= name " -- " ComObjType(obj, "Name") "`n"
;~ MsgBox %list%

MyWbk := ""  ; Clear the variable
for name, obj in GetActiveObjects()
{
    if (ComObjType(obj, "Name") = "_Workbook")                  ; If this object is a workbook...
    {
        ;~ MsgBox, % obj.Path "`n" obj.Name                     ; Show the name and path of this workbook
        if (obj.Path = "C:\Test" && obj.Name = "Book2.xlsx")    ; If this workbook's path and name match...
        {
            MyWbk := obj                        ; Save a reference to this workbook
            break                               ; Break the for-loop
        }
    }
}


; Here we test the reults
if MyWbk                                        ; If MyWbk is not blank show the name
    MsgBox, % MyWbk.Name " was found."
else
    MsgBox, % "The specified workbook was not found."



; GetActiveObjects v1.0 by Lexikos
; http://ahkscript.org/boards/viewtopic.php?f=6&t=6494

;<Paste the GetActiveObjects function definition here>
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: 1081
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

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: 1756
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

wdApp := ComObjCreate("Word.Application")
InputFile := A_Desktop "\New Microsoft Word Document.docx"  ; Example password potected file
Pass := "?#nonsense@$"
Loop
{
    try
    {
        oDoc := wdApp.Documents.Open(InputFile,,,, Pass)
        break
    }
    catch, e
    {
        ; Error:  0x800A1520 - 
        ; Source:		Microsoft Word
        ; Description:	The password is incorrect. Word cannot open the document.
        RegExMatch(e.Message, "^0x[A-F0-9]+", ErrorCode)
        if (ErrorCode & 0x1520 = 0x1520)
        {
            InputBox, OutputVar, Enter Password, The following document requires a password.`n%InputFile%
            if (OutputVar != "")
                Pass := OutputVar
        }
        else
            throw Exception(e.Message, e.What, e.Extra)
    }
}
MsgBox, % oDoc.Name
wdApp.Quit(0)
return
JoeWinograd
Posts: 1081
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: 1756
Joined: 29 Sep 2013, 17:11

Re: MS Office COM Basics

21 Nov 2016, 18:55

Code: Select all

    ;...
    catch, e
    {
        ;...
        if (ErrorCode & 0x1520 = 0x1520)
        {
            ; Do stuff if error = "The password is incorrect."
        }
        else
        {
            ; This error is not "The password is incorrect."
            ; Since we have caught the error, it will not be reported to the user (we have effectively blocked it)
            ; so we need to throw the error again.
            throw Exception(e.Message, e.What, e.Extra)
        }
    }
The Exception function creates an object similar to the object stored in e in the above code snippet.
JoeWinograd
Posts: 1081
Joined: 10 Feb 2014, 20:00

Re: MS Office COM Basics

21 Nov 2016, 19:51

Thanks for the explanation — very helpful!
kon
Posts: 1756
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: 1081
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: 1081
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: 1756
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: 1081
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

oDoc.ShowRevisions:=0
InputDoc[1]:=oDoc.Content.Text
oDoc.close(0)
Now I'm following that up with this:

Code: Select all

wdApp.Quit
ObjRelease(wdApp)
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

 
 f1::Reload
Pause::Pause
f2::ListVars
Pause
!`::
function()
return

function()
{

when:=[]
what:=[]
Nothing:="Nothing to report, have a wonderful day."
TMP:=""
RMRKS:=""
Date1:=""
Date2:=""
EMOUT:=""

EMIN := Clipboard                                       ; Email text var
Loop, Parse, EMIN,`n,`r                             ; parse email by line
{
tmp := StrSplit(A_LoopField, ";")           ; for each line break it into chunks by ";" 
rmrks := tmp.6                                  ; Warn code is in 6th index     
If (InStr(rmrks, "Warning"))                    ; If this is a warning line
{
date1:=StrSplit(tmp.1, "/")                 ; date/time is in DD/MM/YYYY , split it up by "/"
date2= % date1.2 "/" date1.1 "/" date1.3    ;  Rearrange the date into MM/DD/YYYY   
EMOUT .= date2 "`t" rmrks "`n"              ; Push into VAR "11/24/2016 13:40:45    WARNING MESSAGE"
}                                           

}
EMOUT := StrReplace(EMOUT,"""") ; Replace all of the quotes in the var with Null

Loop, Parse, EMOUT,`n,`r    ; Split output by line and then...
{           
tmp := StrSplit(A_LoopField, ["`t"])   ; split lines by tab
when.insert(tmp.1)                  ; insert date/time stamp into "when" array
what.insert(tmp.2)                  ; insert Warn Code into "what" array
}

if (emout!="")                                  ; If there was stuff to put into array
{
XL := ComObjCreate("Excel.Application")    ; create an excel object
wbk := xl.Workbooks.Add                          ; add a workbook to the object
Xl.Visible := True                         ; make it visible
XL.Range("A1").Value := "Time"             ;Create Time header
XL.Range("A:A").columnwidth := "20" 
XL.Range("B:B").columnwidth := "56.86"
XL.Range("B1").Value := "Warning"          ; Create Warning Header
for index in when       
        Xl.Range("A" . index+1).Value := when[index]   ;add everything in the "when" array
for index in what 
        Xl.Range("B" . index+1).Value := what[index]   ;add everything in the "what" array          

	;~ rng := xl.Sheets(1).UsedRange.address
	;~ trgt := xl.Sheets(1).range("c1")
	;~ pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng, xlPivotTableVersion12:=3).CreatePivotTable(trgt, "PivotTable1", ,xlPivotTableVersion12:=3)
	;~ pvt.PivotFields("warning").Orientation := 1
	;~ pvt.PivotFields("warning").Position := 1                
	;~ pvt.PivotFields("time").Orientation := 1
	;~ pvt.PivotFields("time").Position := 2           
	;~ pvt.AddDataField(pvt.PivotFields("Warning"), "Count of Warning",  -4112)    



	;~ Sheet := xl.Sheets(1)
	;~ Sheet.Shapes.AddChart.Select
	;~ wbk.ShowPivotChartActiveFields := false
	;~ xl.ActiveChart.ChartType := 51
	;~ xl.ActiveChart.PivotLayout.PivotTable.PivotFields("Warning").Orientation = xlColumnField
	;~ xl.ActiveChart.PivotLayout.PivotTable.PivotFields("Warning").Position = 1
	





	 
	;~ return


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: 12
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

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: 1756
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

TestText =
    (LTrim Join`r`n
        At: 25/11/2016 23:59:01


        ACTIVITY HISTORY - PAST 24 HOURS:
        NOTE: All times are in Coordinated Standard Time (UTC).
        $"EventDate";"TagName";"Status";"Type";"UserAck";"Description"
        "25/11/2016 00:41:08";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 00:41:08";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 00:41:20";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 00:41:20";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 00:47:12";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 00:47:12";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 00:47:12";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 00:47:12";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 08:04:43";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 08:04:43";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 08:04:55";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 08:04:55";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 08:14:29";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 08:14:29";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 08:14:29";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 08:14:29";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 08:43:13";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 08:43:13";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 08:43:25";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 08:43:25";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 09:00:10";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 09:00:10";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 09:00:10";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 09:00:10";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 09:40:52";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 09:40:52";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 09:41:04";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 09:41:04";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 09:51:16";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 09:51:16";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 09:51:16";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 09:51:16";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 10:19:32";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:19:32";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 10:19:44";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 10:19:44";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 10:25:16";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 10:25:16";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 10:25:16";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 10:25:16";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 10:31:40";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:31:40";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 11:12:04";"Message_200";"ALM";"LVL";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_200";"END";"";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 12:13:28";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 12:13:28";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 12:13:28";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 12:32:44";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 12:32:44";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 12:32:56";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 12:32:56";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 12:34:54";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 12:34:54";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 12:34:54";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 12:34:54";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 14:17:20";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 14:17:20";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 14:17:32";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 14:17:32";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 14:25:44";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 14:25:44";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 14:25:44";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 14:25:44";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 14:36:26";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 14:36:26";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 14:36:38";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 14:36:38";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 14:45:08";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 14:45:08";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 14:45:08";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 14:45:08";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 17:47:35";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 17:47:35";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 17:47:47";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 17:47:47";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 17:50:41";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 17:50:41";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 17:50:43";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 17:50:43";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 17:51:41";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 17:51:41";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 17:51:53";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 17:51:53";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 18:11:41";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 18:11:41";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 18:11:41";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 18:11:41";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 20:20:45";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 20:20:45";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 20:20:59";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 20:20:59";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 20:32:11";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 20:32:11";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 20:32:11";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 20:32:11";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 20:37:29";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 20:37:29";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 20:37:41";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 20:37:41";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 20:50:41";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 20:50:41";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 20:50:41";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 20:50:41";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"

        +++++++++++++++++++++++++++++++++++++++++++++++++++
        This Email has been generated automatically by:
        *** Device Identification:
        47GS Unit #017 (219)
        *** Device Description:
        Fake Fuel
        1234 Plant fake Road 
        fake, FA
        *** Device TCP/IP addresses
        Ethernet: http://192.168.101.666
        PPP:      http://192.168.101.666
        +++++++++++++++++++++++++++++++++++++++++++++++++++
    )

Clipboard := TestText  ; <-- This line is for testing only and should be removed
function()
ListVars
MsgBox
ExitApp

function()
{
    static xlColumnField := 2
         , xlPivotTableVersion12 := 3
         , xlRowField := 1

    Warnings := []
    Nothing:="Nothing to report, have a wonderful day."
    EMIN := StrReplace(Clipboard, """")                 ; Email text var. Remove all quotes.
    
    Loop, Parse, EMIN, `n, `r                           ; Parse email by line
    {
        tmp := StrSplit(A_LoopField, ";")               ; For each line break it into chunks by ";" 
        rmrks := tmp.6                                  ; Warn code is in 6th index     
        If InStr(rmrks, "Warning")                      ; If this is a warning line
        {
            date1:=StrSplit(tmp.1, "/")                 ; Date/time is in DD/MM/YYYY , split it up by "/"
            Warnings.Push( new Warning(date1.2 "/" date1.1 "/" date1.3, rmrks) )
        }
    }
    if Warnings.MinIndex()                                  ; If there was stuff to put into array
    {
        XL := ComObjCreate("Excel.Application")         ; Create an excel object
        Wbk := XL.Workbooks.Add                         ; Add a workbook to the object
        Xl.Visible := True                              ; Make it visible
        
        ; Set Column headings and width
        XL.Range("A1").Value := "Time"                  ; Create Time header
        XL.Range("A:A").columnwidth := "20" 
        XL.Range("B:B").columnwidth := "56.86"
        XL.Range("B1").Value := "Warning"               ; Create Warning Header
        
        ; Create a safe array and copy data into it. Then put the safe array into a range.
        nRows := Warnings.MaxIndex()                    ; The number of rows
        SafeArray := ComObjArray(12, nRows, 2)          ; Create a safearray of the correct size. (Type = 12, Rows = nRows, Columns = 2)
        for i, Warning in Warnings
        {
            SafeArray[i - 1, 0] := Warning.When         ; SafeArray[RowNumber, ColumnNumber] := Value
            SafeArray[i - 1, 1] := Warning.What         ; SafeArray index starts at 0 (not 1)
        }
        Cell := Xl.Range("A2")                          ; The top left cell of the range
        Xl.Range(Cell, Cell.Offset(nRows - 1, 1)).Value := SafeArray  ; Put SafeArray into the Range

        rng := xl.Sheets(1).UsedRange.address
        trgt := xl.Sheets(1).range("c1")
        pvt := xl.ActiveWorkbook.PivotCaches
            .Create(xlDatabase:=1, rng, xlPivotTableVersion12)
            .CreatePivotTable(trgt, "PivotTable1",, xlPivotTableVersion12)
        pvt.PivotFields("warning").Orientation := xlRowField
        pvt.PivotFields("warning").Position := 1                
        pvt.PivotFields("time").Orientation := xlRowField
        pvt.PivotFields("time").Position := 2           
        pvt.AddDataField(pvt.PivotFields("Warning"), "Count of Warning",  -4112)
        Sheet := xl.Sheets(1)
        Sheet.Shapes.AddChart.Select
        wbk.ShowPivotChartActiveFields := false
        xl.ActiveChart.ChartType := 51
        xl.ActiveChart.PivotLayout.PivotTable.PivotFields("Warning").Orientation := xlColumnField
        xl.ActiveChart.PivotLayout.PivotTable.PivotFields("Warning").Position := 1
    }
}

class Warning
{
    __New(When, What)
    {
        this.When := When
        this.What := What
    }
}
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: 12
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

Key:=Range(A2:A14050)
Here's the whole line for referrence:

Code: Select all

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

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

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: 1756
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

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Time")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveChart.PivotLayout.PivotTable.PivotFields("Time").AutoGroup
    ActiveChart.PivotLayout.PivotTable.PivotFields("Minutes").Orientation = _
        xlHidden
    ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartTitle.Select
    Selection.Delete
End Sub


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: 1756
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

Clipboard := GetTestText()  ; <-- This line is for testing only and should be removed
function()
ExitApp

function()
{
    Warnings := ParseWarnings()
    Nothing := "Nothing to report, have a wonderful day."
    
    if Warnings.MinIndex()                              ; If there was stuff to put into array
    {
        Chart := CreateChart(Warnings)
        CreateEmail(Chart)
    }
}

ParseWarnings()
{
    Warnings := []
    EMIN := StrReplace(Clipboard, """")                 ; Email text var. Remove all quotes.
    Loop, Parse, EMIN, `n, `r                           ; Parse email by line
    {
        tmp := StrSplit(A_LoopField, ";")               ; For each line break it into chunks by ";" 
        rmrks := tmp.6                                  ; Warn code is in 6th index     
        If InStr(rmrks, "Warning")                      ; If this is a warning line
        {
            date1:=StrSplit(tmp.1, "/")                 ; Date/time is in DD/MM/YYYY , split it up by "/"
            Warnings.Push( {"When": date1.2 "/" date1.1 "/" date1.3, "What": rmrks} )
        }
    }
    return Warnings
}

CreateChart(Warnings)
{
    static xlColumnClustered := 51
         , xlColumnField := 2
         , xlCount := -4112
         , xlDatabase := 1
         , xlHidden := 0
         , xlPivotTableVersion12 := 3
         , xlRowField := 1
    
    XL := ComObjCreate("Excel.Application")         ; Create an excel object
    Wbk := XL.Workbooks.Add                         ; Add a workbook to the object
    Xl.Visible := True                              ; Make it visible
    Sheet := xl.Sheets(1)                           ; Save a reference to this sheet
    
    ; Set Column headings and width
    Sheet.Range("A1").Value := "Time"               ; Create Time header
    Sheet.Range("A:A").columnwidth := "20" 
    Sheet.Range("B1").Value := "Warning"            ; Create Warning Header
    Sheet.Range("B:B").columnwidth := "56.86"
    
    ; Create a safe array and copy data into it. Then put the safe array into a range.
    nRows := Warnings.MaxIndex()                    ; The number of rows
    SafeArray := ComObjArray(12, nRows, 2)          ; Create a safearray of the correct size. (Type = 12, Rows = nRows, Columns = 2)
    for i, Warning in Warnings
    {
        SafeArray[i - 1, 0] := Warning.When         ; SafeArray[RowNumber, ColumnNumber] := Value
        SafeArray[i - 1, 1] := Warning.What         ; SafeArray index starts at 0 (not 1)
    }
    Cell := Sheet.Range("A2")                       ; The top left cell of the range
    Sheet.Range(Cell, Cell.Offset(nRows - 1, 1)).Value := SafeArray  ; Put the SafeArray into the Range

    rng := Sheet.UsedRange.address
    trgt := Sheet.range("c1")
    pvt := xl.ActiveWorkbook.PivotCaches
        .Create(xlDatabase, rng, xlPivotTableVersion12)
        .CreatePivotTable(trgt, "PivotTable1",, xlPivotTableVersion12)
        
    pfWarning := pvt.PivotFields("warning")
        pfWarning.Orientation := xlColumnField
        pfWarning.Position := 1
        pvt.AddDataField(pfWarning, "Count of Warning",  xlCount)

    ; **Is it necessary to set 'pfTime.Orientation' multiple times?
    pfTime := pvt.PivotFields("time")       ; VBA = With ActiveChart.PivotLayout.PivotTable.PivotFields("Time")
        pfTime.Orientation := xlHidden      ; VBA = ActiveChart.PivotLayout.PivotTable.PivotFields("Time").Orientation = xlHidden
        pfTime.Orientation := xlRowField    ; VBA = .Orientation = xlRowField
        pfTime.Position := 1                ; VBA = .Position = 1
        pfTime.AutoGroup                    ; Must be Office version >= 2016
    pvt.PivotFields("Minutes").Orientation := xlHidden  ; ???
        pfTime.Orientation := xlHidden      ; ???
    
    Sheet.Shapes.AddChart
    wbk.ShowPivotChartActiveFields := false
    
    Sheet.ChartObjects(1).Activate
    Chart := wbk.ActiveChart
        Chart.ChartTitle.Delete
        Chart.ChartType := xlColumnClustered
    
    return Chart
}

CreateEmail(Chart)
{
    ; Reference: http://stackoverflow.com/questions/25603864/copy-excel-chart-to-outlook-mail-message
    ; Alternative method: http://www.mrexcel.com/forum/excel-questions/562877-paste-chart-into-email-body.html
    static olMailItem := 0
    
    olApp := ComObjCreate("Outlook.Application")
    Email := olApp.CreateItem(olMailItem)
    Email.Display
    Email.To := "someone@something.foo"
    Email.Subject := "Test"
    
    Chart.ChartArea.Copy
    wEditor := olApp.ActiveInspector.WordEditor
    wEditor.Application.Selection.Paste
}

GetTestText()  ; <-- This function is for testing only and should be removed
{
    TestText =
    (LTrim Join`r`n
        At: 25/11/2016 23:59:01


        ACTIVITY HISTORY - PAST 24 HOURS:
        NOTE: All times are in Coordinated Standard Time (UTC).
        $"EventDate";"TagName";"Status";"Type";"UserAck";"Description"
        "25/11/2016 00:41:08";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:25:16";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 10:25:16";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 10:25:16";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_325";"END";"";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 10:31:28";"Message_321";"ALM";"LVL";"";"Compressor 2 Running"
        "25/11/2016 10:31:40";"Message_320";"ALM";"LVL";"";"Compressor 1 Running"
        "25/11/2016 10:31:40";"Message_324";"END";"";"";"Compressor 1 Ready To Run (Automatic Start)"
        "25/11/2016 11:12:04";"Message_200";"ALM";"LVL";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_200";"END";"";"";"Oil Injector Temperature Warning (Compressor 2)"
        "25/11/2016 12:13:28";"Message_325";"ALM";"LVL";"";"Compressor 2 Ready To Run (Automatic Start)"
        "25/11/2016 12:13:28";"Message_320";"END";"";"";"Compressor 1 Running"
        "25/11/2016 20:50:41";"Message_321";"END";"";"";"Compressor 2 Running"
        "25/11/2016 20:50:41";"Message_324";"ALM";"LVL";"";"Compressor 1 Ready To Run (Automatic Start)"

        +++++++++++++++++++++++++++++++++++++++++++++++++++
        This Email has been generated automatically by:
        *** Device Identification:
        47GS Unit #017 (219)
        *** Device Description:
        Fake Fuel
        1234 Plant fake Road 
        fake, FA
        *** Device TCP/IP addresses
        Ethernet: http://192.168.101.666
        PPP:      http://192.168.101.666
        +++++++++++++++++++++++++++++++++++++++++++++++++++
    )
    return TestText
}
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 6 guests