Concatenate Excel spreadsheets in Access? 2

[acroninj]

I recieve 30+ spreadsheets everyday in Excel format. I am looking to find a way to add a set of rows together from all of the spreadsheets. Once I have them all together, I need to be able to sort by different fields to create reports. Does Anyone have any ideas?

 

[IRstuff]

You could probably do something in VBA to open each worksheet on a specified list, copy the relevant data to the appropriate sheet, etc.

TTFN

 

[acroninj]

Is there a way to open each workbook in a specified folder with VBA?

 

[IRstuff]

I don't see why not. You simply specify the correct path to the file and pass it to VBA.

TTFN

 

[johnwm]

Use the Dir statement to go through all the .xls files in a particular directory - then you don't need to worry about knowing the names.

Dim myFile
myFile = Dir("c:\myDirectory\*.xls")
Do While Len(myFile) > 0
MsgBox myFile ' or process here
myFile = Dir
Loop

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[FrancisL]

In Access Link to each of the xls files
Then create a query that joins them all together
eg
Select * from Sheet1
Union All
Select * from Sheet2
Union All
Select * from Sheet3

Result - a read only query that shows all records in all the sheets
That can be sorted any way you like in another query.