Code: Select all
Workbook := ComObjGet(path)
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...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)
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.
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."
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>
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.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.If you need to split a file path into separate parts to compare with obj.Path and obj.Name, use the SplitPath command.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>
Code: Select all
try oDoc:=ComObjGet(InputFile)
catch
{
MsgBox,4112,Fatal Error,Error trying to access input document:`n%InputFile%
ExitApp
}
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
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)
}
}
Code: Select all
oDoc.ShowRevisions:=0
InputDoc[1]:=oDoc.Content.Text
oDoc.close(0)
Code: Select all
wdApp.Quit
ObjRelease(wdApp)
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
Code: Select all
Criteria1:=Array("5130", "5132", "5134")
Criteria2:=Array(2, "8/1/2016")
Criteria1:=Array("=")
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
}
}
What are the steps to do this manually?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.
Code: Select all
Key:=Range(A2:A14050)
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)
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")
Code: Select all
xSF.Add(wbs.ActiveSheet.Range("A2:A148023"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
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
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
}
Users browsing this forum: No registered users and 9 guests