getting connection names from excel vba

Question

I have code to select pivot names under each worksheet. I'd like to find out how to get the data connection name for each pivot tables.

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

Can you specify what you mean by "data connection name" and can you paste the first few rows of your data? — abcde123483

aevanko aevanko · Accepted Answer · 2011-11-27T02:59:28

To get the data connection name of the current pivot table, use this:

pvt.PivotCache.WorkbookConnection

Note that it will throw an error if the pivot table is not using a workbook connection so you should check first:

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

getting connection names from excel vba

1 Answers