I am trying to create a pivot table, and then a pivot chart from ahk for embedding into an email. I am a total noob, so please forgive my code quality. I am having trouble getting my pivot table set up right, and cannot get a chart to appear for the life of me. I have included references to what posts I have been working from, as well as desired end result at the bottom of this post.
My data is two columns, with (could make it without) headers of Time, and Warning I want to be able to create a chart that shows the amount, and type of warning over time, from there either get a handle on the object and place it into an outlook email, or even just a copy paste would work for me. Please advise how to get this done, I think I am so close.
Edit I updated the code below, 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 to embed, or get it onto the clipboard.
Thank you for any help.
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
}
if (emout="")
Msgbox, %Nothing%
Reload
}
Source forum posts I am working from are
autohotkey dot com/board/topic/149544-table-pivot-vs-table-pivot-chart-com
and
autohotkey dot com/board/topic/125719-com-excel-pivot-table
End result I am looking for to embed into an outlook email:
Sample input:
