Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Table Pivot VS Table Pivot chart COM



  • Please log in to reply
1 reply to this topic
hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

I am trying to make a Table Pivot Chart

This is the File .txt

0,15344G10030M,79632,LN5,FINAL,ModularTest,2015-20-08,11:39:39,P,,
 
0,15344G10031M,79632,LN5,FINAL,ModularTest,2015-20-08,11:39:43,P,,
 
0,15344G10039M,79632,LN5,FINAL,ModularTest,2015-20-08,11:39:51,F,,
 
0,15344G10020M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:40:00,P,,
0,15344G08790M,51522,LN5,FINAL,ModularTest,2015-20-08,11:40:05,P,,
 
0,15344G10029M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:40:19,P,,
0,15344G10040M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:13,P,,
 
0,15344G08791M,51522,LN5,FINAL,ModularTest,2015-20-08,11:40:39,P,,
 
0,15344G09990M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:40:38,P,,
0,15344G10036M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:31,P,,
 
0,15344G10043M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:32,P,,
 
0,15344G09994M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:40:57,P,,
0,15344G10037M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:49,P,,
 
0,15344G10049M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:56,P,,
 
0,15344G10050M,79632,LN5,FINAL,ModularTest,2015-20-08,11:40:40,P,,
 
0,15344G08792M,51522,LN5,FINAL,ModularTest,2015-20-08,11:41:03,P,,
 
0,15344G09928M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:41:12,P,,
0,15344G10025M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:41:16,P,,
0,15344G10044M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:15,P,,
 
0,15344G10046M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:08,P,,
 
0,15344G10047M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:00,P,,
 
0,15344G10007M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:41:42,P,,
0,15344G10034M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:41:35,P,,
0,15344G10042M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:36,P,,
 
0,15344G10052M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:33,P,,
 
0,15344G10053M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:21,P,,
 
0,15344G10055M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:39,F,,
 
0,15344G10022M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:41:54,P,,
0,15344G10059M,79632,LN5,FINAL,ModularTest,2015-20-08,11:41:52,P,,
 
0,15344G10060M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:00,P,,
 
0,15344G10038M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:12,P,,
 
0,15344G10048M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:42:13,P,,
0,15344G10027M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:20,P,,
 
0,15344G10057M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:09,P,,
 
0,15344G10070M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:31,P,,
 
0,15344G10032M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:42:29,P,,
0,15344G10051M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:42:32,P,,
0,15344G10069M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:35,F,,
 
0,15344G10056M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:42:51,P,,
0,15344G10068M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:41,P,,
 
0,15344G10054M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:42:45,P,,
0,15344G10058M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:43:01,P,,
0,15344G10067M,79632,LN5,FINAL,ModularTest,2015-20-08,11:42:59,P,,
 
0,15344G10077M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:00,P,,
 
0,15344G10035M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:43:10,P,,
0,15344G10075M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:17,P,,
 
0,15344G10076M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:20,P,,
 
0,15344G10072M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:43:29,P,,
0,15344G10082M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:24,P,,
 
0,15344G08793M,51522,LN5,FINAL,ModularTest,2015-20-08,11:43:32,P,,
 
0,15344G10071M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:43:46,P,,
0,15344G10081M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:38,P,,
 
0,15344G10083M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:44,P,,
 
0,15344G09741M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:50,P,,
 
0,15344G10045M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:44:02,P,,
0,15344G10080M,79632,LN5,FINAL,ModularTest,2015-20-08,11:43:56,P,,
 
0,15344G08796M,51522,LN5,FINAL,ModularTest,2015-20-08,11:44:01,P,,
 
0,15344G10079M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:05,P,,
 
0,15344G10021M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:44:16,P,,
0,15344G10073M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:44:18,P,,
0,15344G10084M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:15,P,,
 
0,15344G10090M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:25,P,,
 
0,15344G08794M,51522,LN5,FINAL,ModularTest,2015-20-08,11:44:28,P,,
 
0,15344G09848M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:44:35,P,,
0,15344G10091M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:34,P,,
 
0,15344G10026M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:46,F,,
 
0,15344G09898M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:44:54,P,,
0,15344G10093M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:47,P,,
 
0,15344G10094M,79632,LN5,FINAL,ModularTest,2015-20-08,11:44:54,P,,
 
0,15344G10096M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:44:59,P,,
0,15344G08795M,51522,LN5,FINAL,ModularTest,2015-20-08,11:45:06,P,,
 
0,15344G10041M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:03,P,,
 
0,15344G10078M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:45:13,P,,
0,15344G10095M,79632,LN5,FINAL,FINAL_____4,2015-20-08,11:45:15,P,,
0,15344G10100M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:13,P,,
 
0,15344G10105M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:16,F,,
 
0,15344G08797M,51522,LN5,FINAL,ModularTest,2015-20-08,11:45:35,P,,
 
0,15344G10064M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:45:32,P,,
0,15344G10103M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:29,P,,
 
0,15344G10099M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:51,P,,
 
0,15344G10104M,79632,LN5,FINAL,ModularTest,2015-20-08,11:45:43,P,,
 
0,15344G10061M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:45:54,P,,
0,15344G08798M,51522,LN5,FINAL,ModularTest,2015-20-08,11:46:10,P,,
 
0,15344G09899M,79632,LN5,FINAL,WALL-E3TEST,2015-20-08,11:46:13,P,,
0,15344G10092M,79632,LN5,FINAL,ModularTest,2015-20-08,11:46:11,P,,
 
 

this is the code just for Table Pivot

FileSelectFile, SelectedFile, 3, %A_ScriptDir% , Open a file, Text Documents (*.txt; *.doc)
if SelectedFile =
{
MsgBox, The user didn't select anything.
    ExitApp
 
}
else
FileRead, vista1 ,%SelectedFile%
 
stringReplace, vista1, vista1, `n, , All
 
clipboard := StrReplace(vista1, ",", a_tab)
 
xl := ComObjCreate("Excel.Application")                  ;create a handle to a new excel application
xl.Visible := True                              ;by default excel sheets are invisible
xl.Workbooks.Add                                  ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
xl.ActiveWindow.Zoom := 85
 
Xl.Range("B:B").NumberFormat := "@" ;change the column format to 'text'
 
xl.Range("A2").PasteSpecial(-4104)                      ;'-4163' is the constant for values only; XlPasteType = -4104 = all; 
xl.range("a1").select
 
for x,y in ["Time Thru","Serial","NAED","Linea","Estacion","Equipo","Fecha","Hora","Status","Prueba","Lectura"]
xl.cells(1,x).value := y
 
 
 
cnt := xl.Sheets(1).UsedRange.rows.count ;count the used rowsoExcel.Range("A1:Q1").Select
 
xl.Range("A1:K1").Select
 
;~ ;With Selection.Interior
this := xl.Selection.Interior
{
    this.Pattern := xlSolid := 1
    this.PatternColorIndex := xlAutomatic := -4105
    this.Color := 49407
    this.TintAndShade := 0
    this.PatternTintAndShade := 0
}
;~ ;End With
 
this := xl.Selection.Font
{
    this.ThemeColor := xlThemeColorLight2 := 5
this.TintAndShade := 0
    this.Name := "Arial"
    this.Size := 16
 
 
}
 
Xl.Range("A2:Q100").Sort(Xl.Columns(9), 1) ;sort sheet by data in the 'a' column
 
;~ xl.Range("A1:K1").Select
;~ xl.Selection.AutoFilter
 
 
 
xl.range("A:K").Select
 
;~ rng := xl.Sheets(1).UsedRange.address
;~ trgt := xl.Sheets(1).range("R1")
 
;~ pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "Sheet1!R1C1",xlPivotTableVersion12:=3).CreatePivotTable("sheet1!R1C18", "PivotTable1", ,xlPivotTableVersion12:=3)
pvt := xl.ActiveWorkbook.PivotCaches.Create(xlDatabase:=1, "Sheet1!R1C1:R1048576C11",xlPivotTableVersion12:=3).CreatePivotTable("sheet1!R1C18", "PivotTable1", ,xlPivotTableVersion12:=3)
 
 
ComObjError(false)                     ;disable COM error messages for some non available properties 
pvt.PivotFields("NAED").PivotItems("(blank)").Visible := False
 
pvt.PivotFields("NAED").Orientation := 1
pvt.PivotFields("NAED").Position := 1
 
pvt.PivotFields("Status").Orientation := 1
pvt.PivotFields("Status").Position := 2
 
pvt.AddDataField(pvt.PivotFields("Serial"), "Count of Serial", -4112)    ;xlCount := -4112 
 
 
pvt.InGridDropZones := true
pvt.RowAxisLayout(1)         ;xlCompactRow = 0 ; xlOutlineRow = 2
 
I 'm stuck here
/*
Sheets("Sheet1").Select
;~ Cells(1, 18).Select
;~ ActiveSheet.Shapes.AddChart.Select
;~ ActiveChart.SetSourceData Source:=Range("'Sheet1'!$R$1:$T$18")
;~ ActiveWorkbook.ShowPivotChartActiveFields = True
;~ ActiveChart.ChartType = xlColumnClustered
;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("NAED")
;~ .Orientation = xlRowField
;~ .Position = 1
;~ End With
;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
;~ .Orientation = xlRowField
;~ .Position = 2
;~ End With
;~ ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
;~ "PivotTable1").PivotFields("Serial"), "Count of Serial", xlCount
;~ End Sub
*/
 

this is the code VB Macro for Table Pivot Chart

;~ Columns("A:K").Select
;~ ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
;~ "Sheet1!R1C1:R1048576C11", Version:=xlPivotTableVersion12).CreatePivotTable _
;~ TableDestination:="Sheet1!R1C18", TableName:="PivotTable1", DefaultVersion _
;~ :=xlPivotTableVersion12
;~ Sheets("Sheet1").Select
;~ Cells(1, 18).Select
;~ ActiveSheet.Shapes.AddChart.Select
;~ ActiveChart.SetSourceData Source:=Range("'Sheet1'!$R$1:$T$18")
;~ ActiveWorkbook.ShowPivotChartActiveFields = True
;~ ActiveChart.ChartType = xlColumnClustered
;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("NAED")
;~ .Orientation = xlRowField
;~ .Position = 1
;~ End With
;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
;~ .Orientation = xlRowField
;~ .Position = 2
;~ End With
;~ ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
;~ "PivotTable1").PivotFields("Serial"), "Count of Serial", xlCount
;~ End Sub
 

Any Ideas How Can It Work

Thanks in advance

 


Every day exist something new to learn

kon
  • Members
  • 1652 posts
  • Last active:
  • Joined: 04 Mar 2013
✓  Best Answer
; Load data from file ===========================================================================================================
FileRead, FileData, %A_ScriptDir%\test.txt
Data := {}, MaxLine := 0
Loop, Parse, FileData, `n, `r
{
    if (A_LoopField = A_Space)
        continue
    Data.Push(thisLine := StrSplit(A_LoopField, ","))
    if (thisLine.Length() > MaxLine)
        MaxLine := thisLine.Length()
}
ComData := ComObjArray(12, Data.MaxIndex(), MaxLine)        ;Create a COM array with Data.MaxIndex() rows and MaxLine columns
for nLine, Line in Data                                     ;Copy the Data (array) into a COM array
    Loop, %MaxLine%
        ComData[nLine - 1, A_Index - 1] := Line[A_Index]    ;COM arrays start at 0 (not 1)
; Create excel app ==============================================================================================================
xlApp := ComObjCreate("Excel.Application")  ;create a handle to a new excel application
xlApp.Visible := True                       ;by default excel sheets are invisible
wbk := xlApp.Workbooks.Add                  ;add a new workbook w/ standard 3 (default) sheets or # selected in xl options
xlApp.ActiveWindow.Zoom := 85
xlApp.Range("B:B").NumberFormat := "@"      ;change the column format to 'text'
; Place data into Excel =========================================================================================================
TopLeftCell := xlApp.Range("A2")
BotRghtCell := xlApp.Range("A2").OffSet(Data.MaxIndex() - 1, MaxLine - 1)
xlApp.Range(TopLeftCell, BotRghtCell).Value := ComData
;Set Headings ===================================================================================================================
Hdgs := ["Time Thru","Serial","NAED","Linea","Estacion","Equipo","Fecha","Hora","Status","Prueba","Lectura"]
ComHdgs := ComObjArray(12, 1, Hdgs.MaxIndex())
for i, Hdg in Hdgs
    ComHdgs[0, i - 1] := Hdg
TopLeftCell := xlApp.Range("A1")
BotRghtCell := xlApp.Range("A1").OffSet(0, Hdgs.MaxIndex() - 1)
xlApp.Range(TopLeftCell, BotRghtCell).Value := ComHdgs
;================================================================================================================================
Rng := xlApp.Range("A1:K1")
Interior := Rng.Interior
Interior.Pattern := xlSolid := 1
Interior.PatternColorIndex := xlAutomatic := -4105
Interior.Color := 49407
Interior.TintAndShade := 0
Interior.PatternTintAndShade := 0, Interior := ""
Font := Rng.Font
Font.ThemeColor := xlThemeColorLight2 := 5
Font.TintAndShade := 0
Font.Name := "Arial"
Font.Size := 16, Font := ""
xlApp.Range("A2:Q100").Sort(xlApp.Columns(9), 1) ;Sort column 9. xlAscending = 1
pvt := xlApp.ActiveWorkbook.PivotCaches.Create(xlDatabase := 1
, "Sheet1!R1C1:R1048576C11", xlPivotTableVersion12 := 3)
.CreatePivotTable("sheet1!R1C18", "PivotTable1",, xlPivotTableVersion12 := 3)
pvt.PivotFields("NAED").PivotItems("(blank)").Visible := False
pvt.PivotFields("NAED").Orientation := 1
pvt.PivotFields("NAED").Position := 1
pvt.PivotFields("Status").Orientation := 1
pvt.PivotFields("Status").Position := 2
pvt.AddDataField(pvt.PivotFields("Serial"), "Count of Serial", -4112) ;xlCount := -4112 
pvt.InGridDropZones := true
pvt.RowAxisLayout(1) ; xlCompactRow = 0, xlTabularRow = 1, xlOutlineRow = 2
Sheet := xlApp.Sheets("Sheet1")
Cell := xlApp.Cells(1, 18)
Sheet.Shapes.AddChart.Select ;~ ActiveSheet.Shapes.AddChart.Select
xlApp.ActiveChart.SetSourceData(xlApp.Range("'Sheet1'!$R$1:$T$18"))
wbk.ShowPivotChartActiveFields := True ;~ ActiveWorkbook.ShowPivotChartActiveFields = True
xlApp.ActiveChart.ChartType := 51 ;~ ActiveChart.ChartType = xlColumnClustered = 51
PF := Sheet.PivotTables("PivotTable1").PivotFields("NAED") ;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("NAED")
PF.Orientation := 1 ; xlRowField = 1
PF.Position := 1
PF := Sheet.PivotTables("PivotTable1").PivotFields("Status") ;~ With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
PF.Orientation := 1 ; xlRowField = 1
PF.Position := 2
; The following line has already been done (line 56). It will throw an error if you attempt to do it again.
;xlApp.ActiveSheet.PivotTables("PivotTable1").AddDataField(xlApp.ActiveSheet.PivotTables("PivotTable1").PivotFields("Serial"), "Count of Serial", -4112) ; xlCount = -4112