Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Datasource
I have a pivot table link to a query in a MAccess
database. Took me a long time to format the report... Now, when I move the excel file on the server with the database, the pivot table still point to the one on my C: drive. How can I change the datasource without creating a new pivot table and reformating? even when I reassign a new "ActiveWorkbook.PivotCaches.Connection" string, and refresh, doesn't seem to work. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Datasource
Charles,
There are two changes that need to be made to reference a different external Access database file. (1) ActiveWorkbook.PivotCaches(1).Connection = "your_modified_connection_string" (2) ActiveWorkbook.PivotCaches(1).CommandText = "your_modified_commandtext_string" (Important: You must change them in this order) The SQL CommandText string will have embedded in the FROM clause the filepath to the Access database, so it isn't sufficient to just change the connection string. Note, in this simple example I've assumed the PivotCache is Item 1. You will need to reference your PivotCache appropriately. Hope this helps. Troy "Charles" wrote in message ... I have a pivot table link to a query in a MAccess database. Took me a long time to format the report... Now, when I move the excel file on the server with the database, the pivot table still point to the one on my C: drive. How can I change the datasource without creating a new pivot table and reformating? even when I reassign a new "ActiveWorkbook.PivotCaches.Connection" string, and refresh, doesn't seem to work. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table with MS Access as DataSource | Excel Discussion (Misc queries) | |||
datasource from stored procedure to pivot table | Excel Discussion (Misc queries) | |||
determine in datasource or pivot table | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel | |||
Pivot Table - identifying datasource | Excel Discussion (Misc queries) |