前言
本節會介紹通過VBA的PivotCaches.Create方法實現Excel創建新的數據透視表、修改原有的數據透視表的數據源以及刷新數據透視表內容。
本節測試內容以下表信息為例
1、創建數據透視表
語法：PivotCaches.Create(SourceType, [SourceData], [Version])
說明：
SourceType:必填參數，可以是以下 XlPivotTableSourceType 常量之一： xlConsolidation、 xlDatabase 或 xlExternal
SourceData:非必填，新數據透視表緩存的數據。
Version:版本，非必填，可以是常量xlPivotTableVersion2000，對應Excel 2000，也可以是xlPivotTableVersion10、xlPivotTableVersion11、xlPivotTableVersion12、xlPivotTableVersion14、xlPivotTableVersion15分別表示Excel 2002、2003、2007、2010、2013
示例：
根據上表內容，在原sheet2上創建一個數據透視表，起始位置為J1，透視表設置行為名稱、產品編號，列設置為生產年月，值為銷售數量求和，完整的代碼如下：
Sub CreatePivot()' 聲明工作簿、工作表變量Dim wb As WorkbookDim ws As Worksheet' 聲明數據源、透視表目標起始位置、數據透視表變量Dim dataSource As RangeDim datePivot As RangeDim newPivot As PivotTable'設置工作簿為當前文件Set wb = ThisWorkbookSet ws = ThisWorkbook.Worksheets("Sheet2")' 通過A列獲取最大行數Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row' 定義數據源范圍Set dataSource = ws.Range("A1:F" & lastRow)' 定義透視表目的起始位置' 創建一個新的數據透視表Set newPivot = wb.PivotCaches.Create(xlDatabase, dataSource).CreatePivotTable(ws.Range("J1"), "PivotTable123")' 定義透視表的行列值With newPivot.PivotFields("名稱").Orientation = xlRowField.PivotFields("商品編號").Orientation = xlRowField.PivotFields("生產年月").Orientation = xlColumnFieldWith .PivotFields("銷售數量").Orientation = xlDataField.Function = xlSumEnd WithEnd WithEnd Sub
代碼說明：
注意 PivotCaches.Create 是用在workbook后面的方法屬性
CreatePivotTable 用來指定創建的透視表的位置以及透視表的名稱，若想要在一張新的工作表創建，如想在sheet3中創建，則可以將上述代碼中的ws.Range(“J1”)改為ThisWorkbook.Worksheets(“Sheet3”).Range(“A1”),前提是該工作簿中存在Sheet3工作表
2. 修改數據透視表的數據源
如上例類似，修改已有的數據透視表的數據源，修改為A1：F20,完整的代碼如下：
Sub UpdatePivotSourceData()' 聲明工作簿、工作表變量Dim wb As WorkbookDim ws As Worksheet' 聲明數據源、透視表目標起始位置、數據透視表變量Dim dataSource As RangeDim datePivot As RangeDim pt As PivotTable'設置工作簿為當前文件Set wb = ThisWorkbookSet ws = ThisWorkbook.Worksheets("Sheet2")' 設置要修改的數據透視表名稱Set pt = ws.PivotTables("PivotTable123")' 修改數據透視表的數據范圍pt.sourceData = ws.Range("A1:F20").Address(True, True, xlR1C1, True)' 刷新數據透視表pt.RefreshTableEnd Sub
3. 刷新數據透視表
pt.RefreshTable
pt表示對應的數據透視表，如以下代碼：
Sub RefreshPivotDim pt As PivotTableDim ws As WorksheetSet ws = ThisWorkbook.Worksheets("Sheet2")' 設置要修改的數據透視表名稱Set pt = ws.PivotTables("PivotTable123")' 刷新數據透視表pt.RefreshTableEnd Sub
對應的數據透視表名稱
