excel - 从 excel vba 获取连接名称

Question

我有代码可以在每个工作表下选择数据透视名称。我想了解如何获取每个数据透视表的数据连接名称。

Dim ws As Worksheet
Dim pvt As PivotTable
Dim pvf As PivotField
Dim pvi As PivotItem
Dim x As String
Dim conn As WorkbookConnection

Application.ScreenUpdating = False

Worksheets("Log").Activate
Columns("H:L").Select
i = 1
For Each ws In ActiveWorkbook.Worksheets
  If ws.PivotTables.Count > 0 Then
    For Each pvt In ws.PivotTables
      ActiveCell.Offset(i, 0) = ws.Name
      ActiveCell.Offset(i, 1) = pvt.Name
      'ActiveCell.Offset(i, 2) = conn.Name
      i = i + 1
    Next pvt
  End If
Next ws

Answer 1

要获取当前数据透视表的数据连接名称，请使用以下命令:

pvt.PivotCache.WorkbookConnection

请注意，如果数据透视表未使用工作簿连接，则会引发错误，因此您应首先检查:

If pvt.PivotCache.SourceType = xlExternal Then
   ActiveCell.Offset(i, 2) = pvt.PivotCache.WorkbookConnection
End If