Excel Hints

Results 1 to 4 of 4

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

  1. #1

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

    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"

    VB:
     
    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 
    
    
    Formatting tags added by mark007
    Last edited by eddynutz; 07-17-2008 at 11:08 AM.

  2. #2
    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
    VB:
    ImportTextFile strTemp, "!@#", 0 
    ImportTextFile strTemp, "!@#", lngCount 
    
    
    Formatting tags added by mark007
    to
    VB:
    ImportTextFile Path & "\" & strTemp, "!@#", 0 
    ImportTextFile Path & "\" & strTemp, "!@#", lngCount 
    
    
    Formatting tags added by mark007
    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
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  3. #3
    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

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    12,503
    Location
    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
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •