Automate the import of Excel Files into Access 97 Table

[pipmic]

Hi all,

I am trying to automate the import of a number of excel files, which are all housed in the same folder, into an Access 97 table. (see code below)

However the line : sFile = Dir("C:\1 NoN EU MEMBERS 2015\IndiLists", "*.xls") is highlighted in yellow when I try to run. It gives a mismatch error 13.

Grateful for assistance so that I can automate all excel files.

The code I wrote as explained was:-

Private Sub Command8_Click()

Dim sFile As String

sFile = Dir("C:\1 NoN AB MEMBERS 2010\IndiLists", "*.xls")

Do While Len(Dir) > 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "EOI-All", "c:\1\Non AB MEMBERS 2010\IndiLists\" & sFile


sFile = Dir
Loop
End Sub

 

[arnelgp]

should be:


sFile = Dir("C:\1 NoN AB MEMBERS 2010\IndiLists\*.xls")