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.

 

[joel]

My first recommendation would be to import the large files into Acces
which is much better at handling large files. You can add a key fo
month and have all the data into one file. Then you can query th
Access database from excel and get the results you are looking for. O
perform you queries in Access and then export the query results to Exce
worksheet.

If you don't want to do this then yo can have a macro make a summar
worksheet by opening up each of the files. You would probaly want t
setup a userform to select the options you want and then import th
data. there are many way s of importing the data, a query is only on
method.

I don't want to recommend any code yet until you decide the methods yo
want to use

 

[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

 

[joel]

I would think the Access libraries are still installed in you Notebook.
Can you try two things for me

1) VBA Menu - Tools - References - Microsoft Access 11.0 objec
Library

Check if the Access library is one of the references

2) run this simple macro

Sub test()
Set MyApp = CreateObject("Access.application")
MyApp.Visible = True
End Su

 

[Abdul]

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

Thanks