How to Check the Source Type of Pivot Table using VBA

Excel VBA Check Pivot Source

The following snippet could help in getting the source type of the Pivot Table

Sub CheckSourceConnection()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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