External Data

[Guest]

I have pivot tables in Excel reading an external datasource in Access - say
db1 table A.

The Access database was slightly modified, and the file name changed to say
db2, but the contents of table A remain exactly the same.

I open the Excel pivot table, and click Data => Pivot Table and Pivot Chart
=> Back => Get Data but I don't see any way to tell Excel that the data
source has changed to db2 table A. Is there any way to do this without
starting from scratch? I really don't want to have to recreate all the pivot
tables in a new workbook if I can avoid it.

 

[Frank Stone]

hi,
The data source is in Microsoft query. click somewhere
inside your query range then click
tools>get external data>edit query.
If the wizard comes up, go the the last page(4) and choose
view data or edit query in microsoft query.
once in microsoft query go to View>SQL.
edit the file path protion of the database query to the
new database name.
r4gards Frank

 

[Jamie Collins]

The data source is in Microsoft query. click somewhere
inside your query range then click
tools>get external data>edit query.
If the wizard comes up, go the the last page(4) and choose
view data or edit query in microsoft query.
once in microsoft query go to View>SQL.
edit the file path protion of the database query to the
new database name

Frank,
You standard answer to this type of question ignores the fact that the
database path does not always appear in the SQL query text. The
connection string should be changed. See:

http://www.dicks-clicks.com/excel/ExternalData5.htm#ChangeConn

Jamie.

--

 

[Guest]

Frank & Jamie,

When importing data using an existing query, (via Data/Import External
Data/Import Data), the "Select Data Source" dialog box pops up, from which I
select an existing query. Then another box titled "Import Data" pops up,
giving one the options of editing the query and also selecting "Create
PivotTable report...". The trouble is, once the PivotTable has been created
by this direct method, Excel does not recognize anywhere in the PivotTable as
being inside the Query range - therefore the Edit Query button on the
External Data toolbar remains grayed out. The only way I can edit this is go
back to the original query and re-construct the PivotTable again.

If anyone has any suggestions, that would be great!
Andrew