Query from Multiple Workbooks

[Abdul]

Hello,

I have 12 files in a directory with a sheet name Sales containing
Sales data for each month having more than half a million rows in each
workbook sheets

The field names are all the same in all the files like date, item,
itemgroup, branch, unit, salesman, amount etc

I am looking for a way to get a query from all these workbook and get
a summarised report based on criteria like sales by salesman, by
month, by product, by product group, by date, by branch etc.

Is this will be possible through code?. Itry to use MS query but not
successful

Using Excel 2007 and Win XP

Thanks in advance for any help

Abdul

 

[John Bundy]

That amount of data should really be in Access or SQL. Since you have Excel I
will assume you have Access, create a new database and then go to File->Get
external data-> Link tables and select all of the Excel sheets. This will
make a table for each Excel sheet. Tie the sheets together through
relationships (product number or salesman etc) and query it that way. With
the tables linked, you wont have to update the Access DB if the Excel sheets
change.

 

[Marsh]

If you know how to Select Queries in Access, this will be easy.
My suggestion is to import each file into 1 Access database and create a
select query. Create a relationship using using a Junction table and then do
a select query.
There is an Access/Queries group with an excellent collection of MVP's.

 

[Abdul]

Thanks for all the suggestions. Unfortunately I dont have access
installed in my notebook. I am looking for a workareound from within
excel or a way to create a database and tables without having access
installed.

Thanks

 

[Abdul]

For sure Access is not istalled and the object library also not seen

Thanks