如何基于WorkbookQuery创建PivotCache且不加载源数据至工作簿？

核心思路 #

直接通过WorkbookQuery对象作为PivotCache的数据源，利用PivotCaches.Create方法，设置SaveData=False避免将源数据存储到工作簿，同时指定正确的源类型参数。

步骤与代码示例 #

1. 获取目标WorkbookQuery对象 #

先确认你要使用的Power Query查询名称，通过ThisWorkbook.Queries集合获取对应对象。

2. 创建PivotCache并配置关键属性 #

用PivotCaches.Create方法，将SourceType设为xlExternal，SourceData指向WorkbookQuery的OLEDB连接，同时设置SaveData=False禁用数据保存。

示例VBA代码：

Sub CreatePivotCacheFromWorkbookQuery()
    Dim pqQuery As WorkbookQuery
    Dim pivotCache As PivotCache
    Dim queryName As String
    
    ' 替换为你的Power Query查询名称
    queryName = "你的查询名称"
    
    ' 获取指定的WorkbookQuery对象
    Set pqQuery = ThisWorkbook.Queries(queryName)
    
    ' 创建PivotCache，绑定查询的OLEDB连接
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlExternal, _
        SourceData:=pqQuery.OLEDBConnection _
    )
    
    ' 关键设置：不保存源数据到工作簿
    pivotCache.SaveData = False
    ' 允许手动/自动刷新缓存
    pivotCache.EnableRefresh = True
    ' 设置刷新间隔（0为仅手动刷新，按需调整）
    pivotCache.RefreshPeriod = 0
    
    ' 可选：基于缓存创建数据透视表
    ' pivotCache.CreatePivotTable _
    '     TableDestination:=ThisWorkbook.Sheets("Sheet1").Range("A1"), _
    '     TableName:="PQ_PivotTable"
End Sub

补充说明 #

• 关于WorkbookConnection：Power Query查询会自动生成对应WorkbookConnection，名称格式为Query - [你的查询名]，也可通过该连接创建PivotCache，代码示例：

Set pivotCache = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlExternal, _
    SourceData:=ThisWorkbook.Connections("Query - 你的查询名") _
)
pivotCache.SaveData = False

• 验证效果：创建缓存后，工作簿大小不会因源数据显著增加，且工作表中不会出现源数据内容，仅保留PivotCache的连接信息。

内容的提问来源于stack exchange，提问作者user23123862