创建数据透视表和图表在Excel COM for Autohotkey

https://xkcd.com/979/

f1::Reload 
    Pause::Pause 
    f2::ListVars 
    Pause 
    !`:: 
    SetKeyDelay, -1 
    Recipients:="[email protected]" 
    TMP:="" 
    RMRKS:="" 
    Date1:="" 
    Date2:="" 
    City:="" 
    Chart:="" 
    Warnings:="" 
    EMAIL:=clipboard 
    city:=Getcity(EMAIL) ; Get city name 
    Warnings := ParseWarnings(Email) 
     if Warnings.MinIndex()        ; If there was stuff to put into array 
     { 
      Chart := CreateChart(Warnings) 
      CreateEmail(Chart, city,Warnings) 
     } 
     else 
      msgbox , No Warnings 
    ;###################################################################################################################################################################################################################; 
    ;##########################################################################################  Functions  ####################################################################################################; 
    ;###################################################################################################################################################################################################################; 
    ParseWarnings(Email) 
    { 
     Warnings := [] 
     EMAIL := StrReplace(EMAIL, """")     ; Email text var. Remove all quotes. 
     Loop, Parse, EMAIL, `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}) ;Warnings[1].when //// Warnings[1].what 
      } 
     } 
     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 
      Chart.PivotLayout.PivotTable.PivotFields("Warning").Orientation := xlColumnField 
      Chart.PivotLayout.PivotTable.PivotFields("Warning").Position := 1 
     return Chart 
    } 
    CreateEmail(Chart, city,warnings) 
    { 
     ; 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 := "*** Todays Warnings for Your Gain Site in " city " ***" 
     body:=warndata(warnings) 
     header:="`n" "`n" "Data:" "`n" 
     Email.body := header . body 
     Chart.ChartArea.Copy  
     wEditor := olApp.ActiveInspector.WordEditor 
     wEditor.Application.Selection.Paste 
    } 
    GetCity(EMAIL) 
    { 
      Split := StrSplit(EMAIL, "`n", "`r") 
      City := Split[Split.Length()-5] 
     IfNotInString, City, , 
      City := Split[Split.Length()-6] 
      City:=strsplit(city,",") 
      City:=City.1 
      Return City 
     } 
     warndata(warnings) 
     {  
      for i, Warning in Warnings 
       body .= "`n" Warning.When "`t" Warning.What "`n" ; This is not right 
     return body 
     } 

两种可能的建议：

1. 搜索你想要做一个VBA函数（更可能得到什么好搜索结果寻找VBA的建议）
2. 记录一个宏，在Excel中做你想做的事情，然后看看VBA代码t帽子被生成。换句话说，在记录一个宏的时候制作一个图表并删除标题，然后看看它是否给你提供了很好的代码。在某些情况下，这将起作用，在某些情况下不会。

使用这两种方法中的任何一种，都希望能够让您确定需要将哪些代码或函数转换为脚本。