Got this problem that I don't know how to fix. I have a reporting database that imports data from another access database and a number of excel files.

I wrote the excel import routine which was OK, but then found out that the users require an initial load from 100s of excell sheets! These are all in one folder and have the same data formats to be imported into the same table.

Sheets are call statistics So a column of times and columns for calls answed/abandoned etc. Sheet Date is in a single cell and I could do with capturing it as a new column.

Firstly, is there a way to loop through all the sheets in a folder one at a time and import the data?

Secondly Is it possible to grab a date from one cell in the sheet headings that has a date and add it as a new column?

Thanks OzzBoz

You can use Dir() to help looping thru a given folder for Excel files. Then use DoCmd.TransferSpreadsheet

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempTable", strfile, False

Check this Post out.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

Cheers Tim - I was being a bit dumb there (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

All done and real quick to. Formatted and used the file name for the date in the end..


(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thanks.gif)

Sorry for the link. Try it again, HERE.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)