View pivot table external data source in Excel 2007

[Marc Forget]

I'm trying to just view the current external data source of an Excel pivot
table. I can find the path to the Access database and the name of the
database, but I can't find the name of the table or query which actually
forms the basis for the pivot table.

From reading prior posts, I tried using the VBA command
MsgBox ActiveWorkbook.PivotCaches(1).SourceDataFile (or CommandText)
but this just tells me the path and database name again, not the table/query
name.

Is there a way to get this information?

Thank you.

 

[iliace]

Go to the Data tab, and click on Connections. Find the connection
you're looking for, click Properties, go over to the Definition tab,
and look in the Command Text box.

 

[Marc Forget]

Thanks. I actually found it through the PivotTable Tools Options tab by
clicking on Change Data Source and Connection Properties. Your suggestion
works also, but if there are many connections on the workbook, it may not be
obvious which connection to look for.

 

[iliace]

I like to give connections meaningful names, so that I can distinguish
them. But you're right, it can get quite confusing!

 

[Marc Forget]

I'll try to encourage my co-workers to use meaningful connection names also!

Thanks again.