CODEWITHSUNDEEP
excelvba
Dorman Ringold
Dorman Ringold

Reputation: 55

getting connection names from excel vba

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

Upvotes: 2

Views: 5178

Answers (1)

Gaijinhunter
Gaijinhunter

Reputation: 14685

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

Upvotes: 1

Related Questions