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"

    [VBA]
    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]
    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
    [vba] ImportTextFile strTemp, "!@#", 0
    ImportTextFile strTemp, "!@#", lngCount[/vba] to
    [vba] ImportTextFile Path & "\" & strTemp, "!@#", 0
    ImportTextFile Path & "\" & strTemp, "!@#", lngCount[/vba]

    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
  •