Importing from multiple Excel spreadsheets

[Dave_cha]

Hi folks,

I've been asked to write a script which will import 700 excel spread sheets into 1 table. The files will all be in one location and named incrementally (file001.xls, file002.xls etc).
All 700 spreadsheets are identical in their structure. The first row in all 700 files contains the field names. The data is the only thing that changes.
I presume that the TransferSpreadSheet function would be the way to do but I'm really stuck for time and working with this function is new to me. I'm hoping that someone might be able to help me or have a script which I can edit?

Thanks, Dave

 

[Dugantrain]

ok, I played around with it and got this:

Public Function fGet_Files()
Dim xlApp As Object
Dim fs As Object
Dim i As Long
Dim strPath As String
strPath = "C:Your_Filepath_Name"
Set xlApp = CreateObject("Excel.Application")
Set fs = xlApp.FileSearch
With fs
    .newsearch
    .lookin = strPath
    .Filename = "*.xls"
    .Execute
    For i = 1 To .foundfiles.Count
        DoCmd.TransferSpreadsheet acImport, , "tblImports", .foundfiles(i), True
    Next i
End With
Set fs = Nothing
Set xlApp = Nothing
End Function

 

[Dave_cha]

Dugantrain,

Thanks for this. Sorry for not replying sooner....I've been away from the office for the last few weeks.

Rgd's,

Dave

ok, I played around with it and got this:

Public Function fGet_Files()
Dim xlApp As Object
Dim fs As Object
Dim i As Long
Dim strPath As String
strPath = "C:Your_Filepath_Name"
Set xlApp = CreateObject("Excel.Application")
Set fs = xlApp.FileSearch
With fs
    .newsearch
    .lookin = strPath
    .Filename = "*.xls"
    .Execute
    For i = 1 To .foundfiles.Count
        DoCmd.TransferSpreadsheet acImport, , "tblImports", .foundfiles(i), True
    Next i
End With
Set fs = Nothing
Set xlApp = Nothing
End Function