@
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?