PivotCache.CreatePivotTable 方法 (Excel)

创建一个基于 PivotCache 对象的数据透视表。 返回 数据透视表 对象。

语法

表达式。CreatePivotTable (TableDestination、 TableName、 ReadData、 DefaultVersion)

表达 一个代表 PivotCache 对象的变量。

参数

名称	必需/可选	数据类型	说明
TableDestination	必需	Variant	数据透视表目标区域左上角的单元格 (工作表上生成的数据透视表将放置在) 的区域。 目标区域必须位于工作簿（此工作簿包含由 expression 指定的 PivotCache 对象）的某个工作表中。
TableName	可选	Variant	新的数据透视表的名称。
ReadData	可选	Variant	如果为 True ，则创建包含外部数据库中所有记录的数据透视表缓存;此缓存可能非常大。 如果为 False，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。
DefaultVersion	可选	Variant	数据透视表的默认版本。

返回值

数据透视表

备注

有关基于数据透视表缓存创建数据透视表的替代方法，请参阅数据透视表对象的 Add 方法。

示例

此示例基于 OLAP 提供程序创建新的数据透视表缓存，然后基于活动工作表上单元格 A3 处的缓存创建新的数据透视表。

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .CommandType = xlCmdCube 
 .CommandText = Array("Sales") 
 .MaintainConnection = True 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 1 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 1 
 End With 
End With

此示例使用与 Microsoft Jet 的 ADO 连接创建新的数据透视表缓存，然后基于活动工作表上单元格 A3 处的缓存创建新的数据透视表。

Dim cnnConn As ADODB.Connection 
Dim rstRecordset As ADODB.Recordset 
Dim cmdCommand As ADODB.Command 
 
' Open the connection. 
Set cnnConn = New ADODB.Connection 
With cnnConn 
 .ConnectionString = _ 
 "Provider=Microsoft.Jet.OLEDB.4.0" 
 .Open "C:\perfdate\record.mdb" 
End With 
 
' Set the command text. 
Set cmdCommand = New ADODB.Command 
Set cmdCommand.ActiveConnection = cnnConn 
With cmdCommand 
 .CommandText = "Select Speed, Pressure, Time From DynoRun" 
 .CommandType = adCmdText 
 .Execute 
End With 
 
' Open the recordset. 
Set rstRecordset = New ADODB.Recordset 
Set rstRecordset.ActiveConnection = cnnConn 
rstRecordset.Open cmdCommand 
 
' Create a PivotTable cache and report. 
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _ 
 SourceType:=xlExternal) 
Set objPivotCache.Recordset = rstRecordset 
With objPivotCache 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:="Performance" 
End With 
 
With ActiveSheet.PivotTables("Performance") 
 .SmallGrid = False 
 With .PivotFields("Pressure") 
 .Orientation = xlRowField 
 .Position = 1 
 End With 
 With .PivotFields("Speed") 
 .Orientation = xlColumnField 
 .Position = 1 
 End With 
 With .PivotFields("Time") 
 .Orientation = xlDataField 
 .Position = 1 
 End With 
End With 
 
' Close the connections and clean up. 
cnnConn.Close 
Set cmdCommand = Nothing 
Set rstRecordSet = Nothing 
Set cnnConn = Nothing

此示例使用现有的 WorkbookConnection。

 
 'Get WorkbookConnection object
 Dim conn As WorkbookConnection
 Set conn =  ActiveWorkbook.Connections("MyConnectionName")
 
 'Declare temp variables
 Dim connStr As String
 Dim sqlStr As String
 
 'Store connection string and command text in variables depends on connection type
 If conn.Type = xlConnectionTypeODBC Then
   connStr = conn.ODBCConnection.Connection
   sqlStr = conn.ODBCConnection.CommandText
 End If
  
 If conn.Type = xlConnectionTypeOLEDB Then
   connStr = conn.OLEDBConnection.Connection
   sqlStr = conn.OLEDBConnection.CommandText
 End If
 
 'Create PivotCache
 Dim pcache As pivotCache
 Set pcache = ActiveWorkbook.PivotCaches.Create(xlExternal, conn)
 
 'Then we need to get recordset to create pivot table
 Dim adodb_conn As Object
 Dim rs As Object
 Set adodb_conn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")
 adodb_conn.Open connStr
 rs.Open sqlStr, adodb_conn
 
 Set pcache.Recordset = rs
 'When CreatePivotTable method called the linked WorkbookConnection is losing connection string and command text
 Set pvt = pcache.CreatePivotTable(TableDestination:=Sheets("MySheetName").Cells(1, 1), TableName:="MyPivotTableName")
        
 rs.Close
 adodb_conn.Close
 
 'Restore CommandText and connection string
 pcache.CommandText = sqlStr
 pcache.Connection = connStr
 
 ' Now you have PivotTable that linked with yours WorkbookConnection
 
 

支持和反馈

有关于 Office VBA 或本文档的疑问或反馈？ 请参阅 Office VBA 支持和反馈，获取有关如何接收支持和提供反馈的指南。