Excel VBA Check Pivot Source
The following snippet could help in getting the source type of the Pivot Table
Sub CheckSourceConnection()
Dim pvtCache As PivotCache
Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)
On Error GoTo No_Connection
If pvtCache.SourceType = xlDatabase Then
MsgBox "The data source connection is: " & _
pvtCache.SourceData, vbInformation, "Pivot Table Source"
ElseIf pvtCache.SourceType = xlExternal Then
MsgBox "The data source connection is: " & _
pvtCache.SourceDataFile, vbInformation, "Pivot Table Source"
End If
Exit Sub
No_Connection:
MsgBox "Pivot Table source cannot be determined.", vbInformation, "Pivot Table Source"
End Sub
Posts
