Importing multiple excel spreadsheets into access

[mojo1979]

Hi,

I'm trying to import data from a number of excel spreadsheets (which have the same formating, but saved with different names, and at different locations) into an access table (access 2000 file format in access 2003). Can i create a macro to do this? if so what would it need to consist of?

I need to automate this as much as possible. I will need to run it on excel spreadsheets already completed and on any spreadsheets created in the future.

Any help would be greatly appreciated!

Thanks!

 

[Remou]

Have a look at Transferspreadsheet.

 

[scottian]

i use this

'---------------------------------------------------------------------------------------------------
' import_files
'---------------------------------------------------------------------------------------------------
FileName = ""
FileName = Dir(FileDir & "*.xls")
If FileName <> "" Then
Do Until FileName = ""
DoCmd.TransferSpreadsheet acImport, 8, "PRExcelFilesHoldingArea", FileDir & FileName, True, "A1:U202"
FileName = Dir()
Loop
End If

you could probably modify to look other locations

"My God! It's full of stars...