Show address of Access Database

F

FA

Hello,

I have a pivot table that get its data from an Access database. I want to
show the address of the access database in a cell in the spreadsheet that has
the table.

How can I do that?

Thanks!
 
I

Ildhund

You sound desperate! There are utilities around to extract source data
from a pivot table. Try googling for "pivot table documenter" or
something similar - I can't remember the precise name of one I have used
previously.

Otherwise, the following in a standard module should park the source
data for PTs on the active sheet in column "A". Extract what you need
from the resulting strings.

Option Explicit
Dim i As Integer, Pvt As Object
Sub PivotSource()

If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
i = 1
For Each Pvt In ActiveSheet.PivotTables
Cells(i, 1) = Pvt.SourceData
i = i + 1
Next
End Sub

Noel

FA wrote
 
I

Ildhund

Just to be on the safe side, the code I gave will of course fail if your
pivot table starts at A1 . . . but then you'd already worked that out,
hadn't you!

Noel

Ildhund wrote
 
F

FA

Thanks Noel.
I coppied the code in the MVB window but do not know how to make it run.
Any suggestions?

Thanks FA
 
I

Ildhund

On the sheet where your pivot table is, type Alt+F8, select
"PivotSource" in the window and click "Run" (or whatever the top button
on the right is called in your flavour of Excel).

Noel

FA wrote
 
Top