Solved: Run-time error '53': File Not Found

[eddynutz]

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 

VBA tags courtesy of www.thecodenet.com

[JimmyTheHand]

Hi and welcome to VBAX

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 

VBA tags courtesy of www.thecodenet.com

to

ImportTextFile Path & "\" & strTemp, "!@#", 0 
ImportTextFile Path & "\" & strTemp, "!@#", lngCount 

VBA tags courtesy of www.thecodenet.com

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]

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... Thanks again!
eddy

[mdmackillop]

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