从Pivot表创建列图表

I am trying to create Column Chart from Pivot table.

我正在尝试从Pivot表创建列图表。

I am able to generate the Pivot table from my code by recording the macros.

我可以通过记录宏从代码中生成数据透视表。

I tried in the same way to generate the Chart by recording the macros, but it was confusing.

我尝试用同样的方法通过记录宏来生成图表，但这让人困惑。

So, I tried my own code, I am not successful. Could anyone suggest how i could generate an Chart from Pivot table,

所以，我尝试了我自己的代码，我不成功。谁能告诉我如何从数据透视表中生成图表，

Below is the code used for generating the Pivot table

下面是生成Pivot表的代码

Sub AutoPivot()

Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim pvtsht              As Worksheet

' set Pivot Cache for Pivot Table
' Your range is static, there are ways to refer to a dynamic range
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Preparation sheet!R1C1:R1048576C8")

' set the Pivot table's sheet
Set pvtsht = Worksheets("CAT_Pivot")


' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = pvtsht.PivotTables("PivotTable1") ' check if "PivotTable7" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PvtTbl Is Nothing Then ' Pivot table object is nothing >> create it            
    ' create a new Pivot Table in "PivotTable4" sheet
    Set PvtTbl = pvtsht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=pvtsht.Range("A3"), TableName:="PivotTable1")

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Colour"), "Count of Colour", xlCount

      With PvtTbl.PivotFields("Category")
          .Orientation = xlRowField
          .Position = 1
      End With
      With PvtTbl.PivotFields("Colour")
          .Orientation = xlColumnField
          .Position = 1
      End With
      With PvtTbl.PivotFields("Category")
          .PivotItems("DG").Visible = False
          .PivotItems("DG").Visible = False
          .PivotItems("DG-Series").Visible = False
          .PivotItems("gn").Visible = False
          .PivotItems("yl").Visible = False
          .PivotItems("(blank)").Visible = False
      End With
      With PvtTbl.PivotFields("Colour")
          .PivotItems("(blank)").Visible = False
      End With
Else
     ' just refresh the Pivot cache with the updated Range
     PvtTbl.ChangePivotCache PvtCache
     PvtTbl.RefreshTable        
End If

End Sub

Below is the code, i used for generating the Chart from Pivot table.

下面是代码，我用它来从Pivot表生成图表。

Sub Autochart()

    Dim chobj As ChartObject        
    Dim ch As Chart        
    Dim pvtsht As Worksheet

    Set pvtsht = Sheets("CAT_Pivot")        
    Set chobj = pvtsht.ChartObjects.Add(300, 200, 550, 200)        
    '300 left, 220 top, 550 width, 200 height

    Set ch = chobj.Chart        
    ch.SetSourceData pt.PvtTbl        
    ch.ChartType = xlColumn        
    chobj.Name = "EChart1"

End Sub

1 个解决方案

#1

In order to set the ChartObject.Chart.SetSourceData, you need to set it to the TableRange2 of your desired PivotTable.

为了设置chartobject。SetSourceData，需要将它设置为所需数据透视表的TableRange2。

Also, since this is a new sub, it doesn't recognize the local variable PvtTbl you have Set in Sub AutoPivot, so you need to Set it again in this Sub.

而且，由于这是一个新的子，它不认识子AutoPivot中设置的局部变量PvtTbl，所以需要在这个子集中重新设置它。

Last, there is no chart type xlColumn, you need to select one from the available valid options, suce as xlColumnClustered or xlColumnStacked, etc.

最后，没有图表类型xlColumn，您需要从可用的有效选项中选择一个，例如xlcolumnclusters或xlcolumnstack等。

Code

代码

Sub Autochart()

Dim chtObj      As ChartObject
Dim PvtSht      As Worksheet
Dim PvtTbl      As PivotTable

' set the Pivot sheet
Set PvtSht = Sheets("CAT_Pivot")

' set the Pivot Table object
Set PvtTbl = PvtSht.PivotTables("PivotTable1")

' set the Chart Object
Set chtObj = PvtSht.ChartObjects.Add(300, 200, 550, 200)

' modify ChartObject properties
With chtObj
    .Chart.SetSourceData PvtTbl.TableRange2 ' set the chart's data range to the Pivot-Table's TableRange2
    .Chart.ChartType = xlColumnClustered
    .Name = "EChart1"
End With

End Sub

#1