Transfer Spreadsheet - File Name Argument

[thickglasses]

I have tried to modify some code I found on this site in order to iterate through a directory and load .xls files into a table. The error message I get states "Run-Time error 2522, the action or method requires a file name argument".... The data
I simply want to load one or more .xls files into the Access table with this code that will fire with a button's on_click() event. I am running Win95 and Access 97.
My code is as follows:
***************
Dim fs, i

Set fs = Application.FileSearch
With fs
.LookIn = "c:\data\inbox\"
.SearchSubFolders = False
.FileName = "*.xls"
If .Execute() > 0 Then
Do
MyPath = "c:\data\inbox\" ' Set the path.
NewPath = "c:\data\archive\" 'Set the path for used files
MyName = Dir(MyPath & "*.xls", vbNormal)
DoCmd.TransferSpreadsheet acImport, 8, "tblTransfer", MyName, False
FileCopy MyPath & MyName, NewPath & MyName
Kill MyPath & MyName
Loop
Else
MsgBox "There were no more files found."
End If
End With
*********************

I am new to VBA and any help will be greatly appreciated.

regards,
ThickGlasses

 

[nickjar2]

have u tried putting a beakpoiint so u can see where the error occurs?

Nick

 

[CosmoKramer]

If I'm reading this correctly, it looks like you're using a wildcard in this statement where it requires an explicit file name:

MyName = Dir(MyPath & "*.xls", vbNormal)
DoCmd.TransferSpreadsheet acImport, 8, "tblTransfer", MyName, False