Get Pivot Table Source Data Path

[jojones]

Hi there

I have several pivot tables linked to access dbs. Is there anyway to retrieve the path of the db each of the pivot tables are linked to using VBA?

Thanks in advance
Jo

 

[xlbo]

Hi - try this
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
strSource = pt.SourceData
MsgBox strSource
Next pt
Next ws

Obviously, you can write the source somewhere else rather than msgbox it. Once you've got the source, the path should be reasonably easy to strip out, using FIND / LEFT / MID etc

HTH
~Geoff~

 

[jojones]

I think I got this working successfully when I got this response. Now I am trying to do it and am getting an error:

Run Time Error (13) - Type Mismatch

at line:

strSource = pt.SourceData

When this error occurs I have strSource declared as a string. If I don't declare it, vba makes it a variant/string and I can see the path in the Locals window, but when I try to do anything with the variant, it throws the same error, but at the line which is trying to deal with the code.

any ideas?