Creating Pivot table and chart In Excel COM

Get help with using AutoHotkey and its commands and hotkeys
SmokeyTBear
Posts: 4
Joined: 23 Mar 2015, 18:06

Creating Pivot table and chart In Excel COM

27 Nov 2016, 07:46

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.

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)    


    Ctrgt := xl.Sheets(1).range("D10")
Sheet := xl.Sheets(1)
Sheet.Shapes.AddChart.Select
xl.ActiveChart.SetSourceData(xlApp.Range(rng))
wbk.ShowPivotChartActiveFields := True
xl.ActiveChart.ChartType := 51
PF := Sheet.PivotTables("PivotTable1").PivotFields("Time")
PF.Orientation := 1
PF.Position := 2
PF := Sheet.PivotTables("PivotTable1").PivotFields("Warning") 
PF.Orientation := 1 
PF.Position := 1

PF := Sheet.PivotTables("PivotTable1").PivotFields("Count of Warning") 
PF.Orientation := 1 
PF.Position := 3
}
if (emout="")
Msgbox, %Nothing%

Reload
}
Source forum posts I am working from are

autohotkey.com/board/topic/149544-table-pivot-vs-table-pivot-chart-com

and

autohotkey.com/board/topic/125719-com-excel-pivot-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
User avatar
Jovannb
Posts: 250
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: Creating Pivot table and chart In Excel COM

29 Nov 2016, 11:20

Hi,

that's when an example is too complicated to get it within a few minutes or if it's unclear what the problem/the question is.

J.B.
AHK-Release 1.1.30.03 Ansi 32-bit, Win10 (1903 18362.295) 64-bit, german
burque505
Posts: 1396
Joined: 22 Jan 2017, 19:37

Re: Creating Pivot table and chart In Excel COM

01 Aug 2020, 14:32

Guess I'm a little late to the party :D
Maybe this'll help. (You can remove the timer related stuff, probably - I'm using it because I've started it from a C# script. Shouldn't hurt anything, though.)

Code: Select all

#Persistent

StartXL()


return

StartXL()
{
	SetTimer, myLabel, -0
}

myLabel:
xl := ComObjCreate("Excel.Application")
wbk := xl.Workbooks.Open(A_ScriptDir . "\Ptest.xlsx")
xl.Visible := True
pvt1 := ""

pvtSheet := xl.Sheets.Add
pvtSheet.Name := "Pivot"

; ┌─────────────────────────────────────────────────────────────────────────────────┐
; │  +---------------------------------------------------------------------------+  │
; │  ¦  We don't want our script to need to know the number of rows in advance.  ¦  │
; │  ¦  So we dynamically create the range using a function to determine         ¦  │
; │  ¦  the number of rows in use.                                               ¦  │
; │  ¦  Add a row data to the spreadsheet to test this!                          ¦  │
; │  +---------------------------------------------------------------------------+  │
; └─────────────────────────────────────────────────────────────────────────────────┘

rng := "sheet1!R1C1:R" . (xl.Worksheets("Sheet1").UsedRange.Rows.Count) . "C3"

; ┌─────────────────────────────────────────────────────────────────────────────────────┐
; │  msgbox %rng% ; You can use this to debug your string.                              │
; │  A static range would look like "sheet1!R1C1:R11C3", so yours needs to be similar.  │
; └─────────────────────────────────────────────────────────────────────────────────────┘


pvt1 := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, rng,xlPivotTableVersion12:=3).CreatePivotTable("Pivot!RC1", "PivotTable1", ,xlPivotTableVersion12:=3)

; ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
; │  +------------------------------------------------------------------------------------------------------+  │
; │  ¦  We've started our pivot table on the "Pivot" sheet, in Column 1. Each pivot table uses two columns  ¦  │
; │  +------------------------------------------------------------------------------------------------------+  │
; └────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

; ┌──────────────────────────────────────┐
; │  Now to try a few different layouts  │
; └──────────────────────────────────────┘


; ┌─────────────────────────────────────────┐
; │  pvt1.RowAxisLayout(xlCompactRow := 0)  │
; └─────────────────────────────────────────┘
pvt1.RowAxisLayout(xlOutlineRow := 2)
; ┌─────────────────────────────────────────┐
; │  pvt1.RowAxisLayout(xlTabularRow := 1)  │
; └─────────────────────────────────────────┘

pvt1.PivotFields("Name").Orientation := xlRowField := 1
pvt1.PivotFields("Name").Position := 1

; ┌─────────────────────────────────────────────────────────────────┐
; │  pvt1.PivotFields("Name").PivotItems("blank").Visible := False  │
; │  Doesn't work after Excel 2007                                  │
; └─────────────────────────────────────────────────────────────────┘


pvt1.PivotFields("Item").Orientation := xlColumnField := 2
; pvt1.PivotFields("Item").Position := 1
pvt1.PivotFields("Item").Position := 1

pvt1.PivotFields("Date").Orientation := xlPageField := 3
pvt1.PivotFields("Date").Position := 1

pvt1.AddDataField(pvt1.PivotFields("Date"), "Pivot by Date", xlCount := -4112)
; ┌──────────────────────────┐
; │  +--------------------+  │
; │  ¦  -4112 is xlCOUNT  ¦  │
; │  +--------------------+  │
; └──────────────────────────┘

SetTimer
ExitApp
return

Regards,
burque505

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, kczx3, kunkel321, mikeyww and 28 guests