User login

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

Code/Templates/Informaiton provided on this site are without any liabilities. Owner of this website in no event will be held reponsible to damages resulting by using the code/templates/applications/
Copyright © 2003 xlMacros.com - Learn Excel VBA
Microsoft Excel is a U.S. registered trademark of Microsoft Corporation

Excel for Dummies
Spreadsheets for Dummies