VBA Express
Solved: Run-time error '53': File Not Found [Archive] - VBA Express Forum

PDA

View Full Version : Solved: Run-time error '53': File Not Found



eddynutz
07-17-2008, 10:44 AM
I have a workbook that opens txt files in worksheets, one file per sheet.
i use Logfiles = ThisWorkbook.Path so that where it searches within the directory the workbook is located. Works fine when I open excel and navigate to the directory where the workbook exist. When I click directly on the file and I run the macro I get a "Run-time error '53': File Not Found"


Sub Log_Cleaner()
Dim numfiles As String
DeleteAll
FileLoc = ThisWorkbook.Path
FileCountA (FileLoc)

End Sub

Function FileCountA(Path As String) As Long
Dim strTemp As String
Dim lngCount As Long
Dim C3Po As String
Sheets("Variables").Select
C3Po = Range("C3").Value
If C3Po = "" Then
C3Po = "*.*"
End If
strTemp = Dir(Path & "\" & C3Po)
If strTemp = "" Then
MsgBox "No files found that fit Search criteria", , "CM MESSAGE"
Exit Function
End If
ImportTextFile strTemp, "!@#", 0
Do While strTemp <> ""
lngCount = lngCount + 1
strTemp = Dir
If strTemp = "" Then
Exit Function
End If
ImportTextFile strTemp, "!@#", lngCount
Loop

End Function

JimmyTheHand
07-17-2008, 09:58 PM
Hi and welcome to VBAX :hi:

I guess the root of the problem is that Dir function returns only the filename, not a full path. So you probably need to change
ImportTextFile strTemp, "!@#", 0
ImportTextFile strTemp, "!@#", lngCount to
ImportTextFile Path & "\" & strTemp, "!@#", 0
ImportTextFile Path & "\" & strTemp, "!@#", lngCount

HTH

Jimmy

PS1: I say 'probably' because I don't see the code of Sub ImportTextFile, or the declaration of FileLoc.
PS2: C3Po is a funny name for a variable :)

eddynutz
07-18-2008, 06:38 AM
Thanks for the welcome and thanks for the solution, great catch! C3Po was to remind the cell i was using to stuff the variable, C3 why not Po...:moosegrin Thanks again!
eddy

mdmackillop
07-18-2008, 12:11 PM
Hi Eddy,
Welcome to VBAX.
When you post code, you can format it as shown by selecting it and clicking the VBA button. It makes it more readable.
Regards
MD