Monday, July 27, 2009

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()

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

No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.