How do I automatically import multiple Excel spreadsheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a folder into which I am placing multiple excel spreadsheets of daily
usage data (all spreadsheets have the same format). The number of
spreadsheets would typically be 7 (ie, one week), but may vary.
What I'm interested in doing is automating an import macro or similar that
essentially will import data from the first listed spreadsheet into a form,
and then append every subsequent spreadsheet to the end of it, for as many
files as there are in the folder.
Currently I have a macro that will append a spreadsheet, but the problem is
that it requires me to manually import each additional spreadsheet, which can
be quite time consuming when there are a lot of files (eg, several weeks).
Any thoughts?
 
First you are not appending into a form, you are appending to a table.
Perhaps that table is a record source for a form.
What you want to do is not all that difficult, it will just take a little
work to set it up.
I would suggest rather than importing the spreadsheets, you link to them and
use an append query to move the data into the table. When you import a
spreadsheet, it overwrites whatever was in the table previously. This is not
what you want. Also, doing repeated imports, appends, and deletes will
create bloat.

You can use the Dir() function to get a list of all the spreadsheet files in
the directory and loop through that list to get data from all the
spreadsheets. You will find detailed information on the Dir function in VBA
help, including how to loop through the list.

You will need an append query that will copy the data from the spreadsheet
into your table. It doesn't matter what the names of the spreadsheet files
are, you can give each of them the same table name.

So here is the sequence of events:

Using Dir, get the name of the next spreadsheet.
Using TransferSpreadsheet, link to the spreadsheet file using a consistent
table name.
Run the Append query to copy the data into the table.
Use the DeleteObject method to delete the link to the spreadsheet
Repeat until all files have been processed.
 
Back
Top