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

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

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165874

Microsoft Office Help

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" wrote:

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
.

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

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 object
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165874

Microsoft Office Help

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

Thanks



On Jan 2, 4:02*pm, joel wrote:
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 object
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 Sub

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=165874

Microsoft Office Help