Page 3 of 6
Re: MS Office COM Basics
Posted: 13 Nov 2016, 11:03
by RNDLDVL
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.
Re: MS Office COM Basics
Posted: 13 Nov 2016, 15:10
by kon
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
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.
Re: MS Office COM Basics
Posted: 14 Nov 2016, 09:04
by RNDLDVL
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.
Re: MS Office COM Basics
Posted: 21 Nov 2016, 13:28
by JoeWinograd
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
Re: MS Office COM Basics
Posted: 21 Nov 2016, 16:39
by kon
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
Re: MS Office COM Basics
Posted: 21 Nov 2016, 18:43
by JoeWinograd
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
Re: MS Office COM Basics
Posted: 21 Nov 2016, 18:55
by kon
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.
Re: MS Office COM Basics
Posted: 21 Nov 2016, 19:51
by JoeWinograd
Thanks for the explanation — very helpful!
Re: MS Office COM Basics
Posted: 21 Nov 2016, 21:34
by kon
Actually, no need for the Exception function (duh, now that I think about it):
throw e
Re: MS Office COM Basics
Posted: 21 Nov 2016, 23:02
by JoeWinograd
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?
Re: MS Office COM Basics
Posted: 22 Nov 2016, 10:44
by JoeWinograd
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
Re: MS Office COM Basics
Posted: 22 Nov 2016, 11:13
by kon
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.
Re: MS Office COM Basics
Posted: 22 Nov 2016, 11:55
by JoeWinograd
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:
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
Re: MS Office COM Basics
Posted: 29 Nov 2016, 07:56
by SmokeyTBear
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.
Re: MS Office COM Basics
Posted: 29 Nov 2016, 09:46
by RNDLDVL
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.
Re: MS Office COM Basics
Posted: 29 Nov 2016, 17:04
by kon
@
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?
Re: MS Office COM Basics
Posted: 30 Nov 2016, 02:34
by RNDLDVL
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:
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)
Re: MS Office COM Basics
Posted: 30 Nov 2016, 02:56
by kon
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.)
Re: MS Office COM Basics
Posted: 02 Dec 2016, 10:41
by SmokeyTBear
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?
Re: MS Office COM Basics
Posted: 02 Dec 2016, 12:55
by kon
(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 := "[email protected]"
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.)