no files found error

[doran_doran via AccessMonster.com]

I am receiving No file found. but i know files are in the directory.


Private Sub cmdImport_Click()
Dim MyFileSpec, MyPath, fs As String
Dim MyFileName As Variant
Dim FileCounter As Integer
Dim ActiveFile As String

MyPath = "L:\Project\"
MyFileSpec = "trans*.xls"

MyFileName = GetFileList(MyPath & MyFile)

If IsArray(LoadDirFileList) = False Then
MsgBox "No files found"
Exit Sub
Else
For FileCounter = LBound(LoadDirFileList) To UBound(LoadDirFileList)
ActiveFile = MyFileName(FileCounter)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"master", MyName, False
MsgBox "import complete"
Next FileCounter
End If
end sub

 

[Douglas J. Steele]

I agree with Klatuu that it's difficult to comment without knowing what
GetFileList does.

However, try making the following change:

If IsArray(LoadDirFileList) = False Then
MsgBox "No files found"
Exit Sub
Else
For FileCounter = LBound(LoadDirFileList) To UBound(LoadDirFileList)
ActiveFile = MyFileName(FileCounter)
If Len(Dir(ActiveFile)) > 0 Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"master", MyName, False
MsgBox "import complete"
Next FileCounter
End If

Do you really want the MsgBox in the middle of the loop though?