Pivot Table Data Source

[vandido626]

I'm working with a pivot table that is linked to an access db. I would like know what the source data is. Can someone let me know what I have to do? I have tried viewing microsoft query but since its a pivot table the option is faded out.

 

[KWMSeattle]

I haven't tried this with a pivot linking to an Access database, but here is some lame code I put together to give me the server name and cube name for OLAP cube pivots.

Sub GetPivotSource()
Dim pvt As PivotTable, ServerName As String, CubeName As String
 
On Error Resume Next
Set pvt = ActiveCell.PivotTable
    If Err.Number <> 0 Then
        MsgBox "Active cell is not in a pivot table", vbOKOnly, "Pivot Source"
        Exit Sub
    End If
 
    ServerName = Mid(pvt.PivotCache.Connection, 64, Len(pvt.PivotCache.Connection) - 132)
    CubeName = Mid(pvt.PivotCache.Connection, 92, Len(pvt.PivotCache.Connection) - 136)
 
    If ServerName = "" Then
        MsgBox "Pivot table data comes from an Excel table", vbInformation
    Else
        'Show pivot table connection details
        MsgBox "Server:  " & ServerName & vbCr & "Cube:  " & CubeName, vbOKOnly, "Pivot Source"
    End If
 
End Sub

 

[vandido626]

I haven't tried this with a pivot linking to an Access database, but here is some lame code I put together to give me the server name and cube name for OLAP cube pivots.

Sub GetPivotSource()
Dim pvt As PivotTable, ServerName As String, CubeName As String
 
On Error Resume Next
Set pvt = ActiveCell.PivotTable
    If Err.Number <> 0 Then
        MsgBox "Active cell is not in a pivot table", vbOKOnly, "Pivot Source"
        Exit Sub
    End If
 
    ServerName = Mid(pvt.PivotCache.Connection, 64, Len(pvt.PivotCache.Connection) - 132)
    CubeName = Mid(pvt.PivotCache.Connection, 92, Len(pvt.PivotCache.Connection) - 136)
 
    If ServerName = "" Then
        MsgBox "Pivot table data comes from an Excel table", vbInformation
    Else
        'Show pivot table connection details
        MsgBox "Server:  " & ServerName & vbCr & "Cube:  " & CubeName, vbOKOnly, "Pivot Source"
    End If
 
End Sub

says its from an excel table but im sure its not........still nice code that I can use down the line...thanks

 

[RoryA]

If you need to know which tables or queries, you can right-click the table, choose Pivot Table Wizard, press the Back button and then the 'Get Data...' button. If you need to know which database it is connected to, then you need to look at the PivotCache connection in code.

 

[vandido626]

If you need to know which tables or queries, you can right-click the table, choose Pivot Table Wizard, press the Back button and then the 'Get Data...' button. If you need to know which database it is connected to, then you need to look at the PivotCache connection in code.

perfect!

so easy that it makes me feel a bit embarrassed

thanks