excel pivot table linked to external source

[marina madeleine]

I have an excel pivot table which has a live link to an external source
(an Access database). How can you tell to which access table the pivot
table is linked? Is there a place in the pivot table where this info
could be found?

Thanks.

Marina

 

[keepITcool]

if your programming:
it's stored via the connection property of the pivotcache

for manual editing :
select a cell inside the pivottable
rightclick
select Wizard
the wizard will show Page3 of 3
select the "Back" button

by clicking the GetData button on Page2 of 2
you can rerun the query (with an option)
to edit it in MsQuery..
THEN in SQL mode you can see the table..

complicated isn't it?


thus .. try following macro..

Sub ShowPivotConnection()
Dim pt As PivotTable

On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo 0
If pt Is Nothing Then
MsgBox "No PT selected"
Else
MsgBox Replace(pt.PivotCache.Connection, ";", vbNewLine)
End If
End Sub

 

[marina madeleine]

Thank you, KeepItCool!

Marina

 

[Jamie Collins]

edit it in MsQuery..
THEN in SQL mode you can see the table..

I tried this myself and the text in the SQL window was as follows:

{Call MyStoredProc('2001-01-01', '2004-01-01')}

Do I now have to query a system table? Something like:

Dim oConn As Object
Dim oRs As Object

Set oConn = CreateObject("ADODB.Connection")
With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = CONN_STRING
.Open

' adSchemaProcedures
Set oRs = .OpenSchema(16, _
Array(Empty, Empty, "MyStoredProc", Empty))

oRs.ActiveConnection = Nothing
.Close

End With

MsgBox oRs!PROCEDURE_DEFINITION

complicated isn't it?

Things aren't always straightforward <g>.

Jamie.

--