Show address of Access Database
 

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!

Show address of Access Database
 

Do I need to clarify further?
Please help...

"FA" wrote:

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!

Show address of Access Database
 

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

Do I need to clarify further?
Please help...

"FA" wrote:

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!

Show address of Access Database
 

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

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

Do I need to clarify further?
Please help...

"FA" wrote:

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!

Show address of Access Database
 

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

Thanks FA


"Ildhund" wrote:

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

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

Do I need to clarify further?
Please help...

"FA" wrote:

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!

Show address of Access Database
 

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

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

Thanks FA


"Ildhund" wrote:

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

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

Do I need to clarify further?
Please help...

"FA" wrote:

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!