Append data from multiple Excel Workbooks

[CutAndPaste]

Hi,
I've got to append some data from an Excel workbook, OrganisationName.xls into two tables in a database. In the workbook I've labelled two ranges, RangeOrganisationInfo (to go into tblOrganisationInfo) and RangeEmployeeDetails (to go into tblEmployeeDetails). The data must be appended in this order due to referential integrity rules.

I can do this one Workbook at a time by creating a link to each of the named range in a workbook and then running an append query on this link to the relevant database table. Then I link to the next workbook and repeat the process, this works fine.

However, as I've got lots of Organisations to deal with, I'd like to search a folder (e.g. c:\MyDatabase\RawData) for Workbooks, then for each workbook, append the data from the named ranges into the relevant tables in my database and then move the processed workbook to another folder (e.g. c:\MyDatabase\ProcessedData).

Example Folder Layout
c:\MyDatabase
Holds the database
c:\MyDatabase\RawData
Holds Workbooks awaiting procesing
c:\MyDatabase\ProcessedData
Holds Workbooks that have been processed.

How can I go about this?

Many thanks!

 

[Travis]

1. You will need to look into code to link/unlink the Spreadsheets
2. Use the "DIR" function to loop through your Raw Data Folder
3. Use "FileCopy" to copy the file to the Processed Data
4. Use "Kill" to delete it from the Raw Data folder after you copy it.