Batch import excel files

[Question Boy]

Would anyone have a good method to import a large number of standardized
excel files?

My boss finally saw the light, that individual workbooks do not give proper
oversight and now wants me to develop a small db to replace the workbooks.
He also wants me to import all the existing information (1000+ spreadsheets).
They are all setup the same way (layout). How can I automate this task? I
really cannot conceive doing this manually.

Thank you,

QB

 

[pietlinden]

Would anyone have a good method to import a large number of standardized
excel files?

My boss finally saw the light, that individual workbooks do not give proper
oversight and now wants me to develop a small db to replace the workbooks. 
He also wants me to import all the existing information (1000+ spreadsheets).
 They are all setup the same way (layout).  How can I automate this task?  I
really cannot conceive doing this manually.

Thank you,

QB

Create an import specification to map the sheet to a table in your
database. (one for each sheet that goes into a different table). Then
once you have that, you can use Allen Browne's code to loop
directories and subdirectories and grab all the excel files using
DIR(). Then you would use TransferSpreadsheet if the import is easy.
Otherwise, you'd have to do something like create a query on an excel
file, and change the SQL in code and execute it. (Or use ADO). How's
that for a vague confusing answer?