Pivot table data source

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

How can I edit the source (mdb file) for a pivot table in
Excel?

We have several sources of data stored on a local hard
drive and need to be able to move them to the server for
all to use. We also develop in one location and then move
to a production environment. I will need to edit the
source for this also.

I was able to find something on the internet about a .oqy
file for editing the source of an olap. I assume I will
be looking for something similar to this. Where would the
query file be stored by default? I'm using Excel local to
my computer, but the files are on the server.

Thank you in advance.
 
Here's one way. Run the following in your development environment,
having changed the code to use the correct pivot table. It will print in
the Immediate window the connection string of the pivot cache
corresponding to the pivot table you specify (PivotTable1 on
Sheet1in this example).

Public Sub ResetPivotTableConnection()
Dim pc As PivotCache
Dim pt As PivotTable
Dim str As String
Set pt = Sheet1.PivotTables("PivotTable1")
Set pc = ThisWorkbook.PivotCaches(pt.CacheIndex)
Debug.Print pc.Connection
End Sub

Now replace the Debug.Print statement with code to reset the
connection string. Use the string in the Immediate window as
your template. The only thing you need to change is the
database path specified in the DBQ= parameter. Most of the
parameters do not need to be specified because they will
be set to the defaults. For example on my PC
it would be something like:
str = "ODBC;DSN=MS Access Database;"
str = str & "DBQ=C:\Test copy of db1.mdb;"
pc.Connection = str

Regards,
Rob Rutherford
 
Back
Top