Import multiple excel files

  • Thread starter Thread starter Miri
  • Start date Start date
M

Miri

Can anyone tell me how to import multiple files into an
Access 2000 db. I have over 100 files I need to import.
Thanks.
 
I had to remove the ".xls" on the file names to use this.
Access doesn't like period in table names. You might be
able to trim that off. I hope this can help

This creates the tables with the same name as in the
directory C:\testimport\ without the ".xls"

Private Sub Command0_Click()
Dim filename As String
filename = Dir("c:\testimport\*.*")

Do Until filename = ""
DoCmd.TransferSpreadsheet acImport, 8, filename,
"c:\testimport\" & filename, True, ""
filename = Dir
Loop
End Sub
 
I got it now without renaming the files

Private Sub Command0_Click()
Dim filename As String
filename = Dir("c:\testimport\*.*")

Do Until filename = ""
DoCmd.TransferSpreadsheet acImport, 8, Left(filename,
InStr(filename, ".") - 1),"c:\testimport\" & filename, True, ""
filename = Dir
Loop
End Sub
Click to expand...

I tested this and it works

Jim
 
This will import into the table named JIM. If it doesn't
exist the first import will create it. I hope this will help.

This creates the tables with the same name as in the
directory C:\testimport\ without the ".xls" without the xls

Private Sub Command0_Click()
Dim filename As String
filename = Dir("c:\testimport\*.*")

Do Until filename = ""
DoCmd.TransferSpreadsheet acImport, 8, "JIM",
"c:\testimport\" & filename, True, ""
filename = Dir
Loop
End Sub


Jim
 
It worked perfectly. Thanks so much.
-----Original Message-----
This will import into the table named JIM. If it doesn't
exist the first import will create it. I hope this will help.

This creates the tables with the same name as in the
directory C:\testimport\ without the ".xls" without the xls

Private Sub Command0_Click()
Dim filename As String
filename = Dir("c:\testimport\*.*")

Do Until filename = ""
DoCmd.TransferSpreadsheet acImport, 8, "JIM",
"c:\testimport\" & filename, True, ""
filename = Dir
Loop
End Sub


Jim

.
Click to expand...
 
This is a function I would really like to use, however I can get it to work. Is this a module I would create? I know this is a beginner question so I may be in the wrong group.
Thanks for any help.
 
Yes you would need to create a new module.
-----Original Message-----
This is a function I would really like to use, however I
Click to expand...
can get it to work. Is this a module I would create? I
know this is a beginner question so I may be in the wrong
group.
 
Back
Top