What's the best way to consolidate data from multiple Excel workbooks?

What's the best way to consolidate data from multiple Excel workbooks?

As a quick fix to your project, I suggest linking Excel workbooks to Access (use Link Table from within Access).

Once each worksheet/workbook has been linked which is (normally) read only, you can treat each file as if it were a table.

The only limitation will you find is that you can not change the design (as in a table) and won't be able to set indexes for a field.

But when you create a query your can join the tables and run reports.

Just make sure each table has a common field (with the same data type) that you wish to join in order for the relational rules to work. See article: http://www.about-access-databases.com/access-relat...

Ideally, you need to spend time creating real tables which have an 'automated' link (perhaps macros) to Excel which dynamically draws down data on demand in order to take advantage of access functionality and performance benefits.

If you need more info, please drop me an email.

Ben Beitler

I can think of two methods. The first is to "merge" the workbooks into one, then create the PivotTable. The second is to create a PivotTable in a fourth workbook, using external data sources and selecting the other workbooks.