Data from Access

[Guest]

I want to take the data from an Access table and update a sheet where I
already have data which is used to feed quite a few pivot tables (380 to be
exact). After this I want to update all the pivot tables to reflect the
changes. I already know how to systematically go through the pivot tables on
each sheet. How do I pull in the data from access? I am using access XP (the
db is 2000 by default) and Excel XP. Any help you can provide would be great!
Thanks!

 

[Guest]

The easiest way to do this is using MSQuery - Data menu, Get External Data,
New Database Query (at least in Excel 2000 - perhaps menu items have changed
in XP). It will ask for your data source, choose MS Access and specify the db
file. You can set up a query in MSQuery (which looks and acts similar to
Query design in Access), then use the menu item "Return Data to Microsoft
Excel." The data comes back in list format. Right-click on the list and you
can set the query properties - or refresh the data at any time. You can also
set it to refresh the data every time the Excel file opens, or you can use
VBA to refresh. Once set up it is pretty handy and should let you get the
current data whenever you need it - you can then use it to feed all those
PivotTables (geez, glad I didn't need to set them all up!).

HTH - K Dales

 

[Nick Hodge]

You can select an external data source straight from the pivot table menu
option too. Saves bringing it into Excel as data and then summarizing.
This also 'gets over' the 65536 row limit as, providing you don't display
more than 32,000 records, the underlying data can be 100,000s of records.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)