Importing Excel Files into Access

  • Thread starter Thread starter Marc_STL via AccessMonster.com
  • Start date Start date
M

Marc_STL via AccessMonster.com

Hello,

I know there have been several other threads, but I have not been successful
at finding the solution.

I have several Excel files that I would like to import into Access.

All the files that will be imported will be kept in a separate folder.
All the files that will be imported will be combined into a single table.
All the files that will be imported will have the same column headings.

What would be the most appropriate code to write?

I know very little about writing code.

I saw one very simple code I could use, but when I tested it out it did not
work and now I can’t find the thread I got the code from.

Thanks.
 
Does your table have the EXACT same column headings as filed names ? Then
you can use DoCmd.TransferSpreadsheet. Since you have little experience,
this is the easiest approach.

For more complicated imports, you need to use Excel automation and loop
through the spreadsheet to read and write each row.
 
I thought about using that command, however, each file name is different and
will be different each time I run the process so I am not sure how that would
work.

Yes, each spreadsheet that will be put into a table has the exact same column
headings.
 
Presumably, you want to present the user with the standard OPEN dialog and
let them selct the file. Then, your DoCmd.TransferSpreadsheet will import
the selected file as desired.
 
That is not an option for the user. First, they do not know how to use
Access, and Secondly, giving them Access is not an option either

This is why I want to develop this for them.

Thanks.
 
You could check out the DIR() function. Using it, you could cycle through
all the *.xls files, link to each in turn using the same MS Access table name,
run an Append query to transfer the data to the MS Access table, and then
delete the link.

MS Access help gives an example of how the Dir() function can do cycle
through the files (at least it does in A2000).

HTH

John
 
Johns,

Thanks for the help. However I do not see this in Access 2003. Once I do
that procdure how would I indicate the table to import to?

J_Goddard said:
You could check out the DIR() function. Using it, you could cycle through
all the *.xls files, link to each in turn using the same MS Access table name,
run an Append query to transfer the data to the MS Access table, and then
delete the link.

MS Access help gives an example of how the Dir() function can do cycle
through the files (at least it does in A2000).

HTH

John
That is not an option for the user. First, they do not know how to use
Access, and Secondly, giving them Access is not an option either
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
Back
Top