Import multiple excel files

[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.

 

[Jim/Chris]

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

 

[Jim/Chris]

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

I tested this and it works

Jim

 

[Miri]

Thanks. This is exactly what I needed. What if I wanted
to import into the same table.

 

[Jim/Chris]

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

 

[Miri]

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

.

 

[Patrick]

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.

 

[Guest]

Yes you would need to create a new module.

-----Original Message-----
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.