Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Run TIme Error- File Not Found when it exists

    I run this module 100s of times a day but occasionally get hung up in Excel 2010/VBA. The error is

    Run-time error '1004:
    'P06_0030.dat' could not be found. ...

    When I go to the debug mode in VBA and F8 the command executes OK.

    Here are the pertinent commands:

    Sheets("Param").Select
    Dim Newfile As String
    Newfile = Range("H2").Value
    Sheets("P06_0030.csv").Select

    Workbooks.OpenText FileName:=Newfile

    the value in H2 is C:\LSicom\Raw\03611\P06_0030.dat


    Thanks in advance for your thoughts.
    Bill

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Dump question can you see the file in said folder

    Also you are trying to open .dat file but the code is selecting a .csv file
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2011
    Posts
    2

    Run time error

    Yes the file is there. And the data is in a .dat file format. The reason the sheet ends .csv relates to how it will b outputed in later processing.
    Last edited by wwcrew; 09-12-11 at 11:14.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I do not have a solution for your VBA code that fails to find an existing file, but I would add code for testing to see if several attempts at getting access to the file succeed. The test code uses the DIR command prior to opening the text file, and if the file is not found, makes 2 more attempts at finding the file. You may want to exit, as I have coded it, or continue in the procedure if DIR fails to locate the file.

    This test process may give you some insight into what is going on: does the DIR test always succeed and Workbooks.OpenText still occasionally fail? Does using the DIR command clear up the problem completely? I guess there is no logical reason why it should, but running VBA code 100s of times and seeing it fail occasionally might need an illogical workaround.
    Good luck.


    Code:
    Sub Test_Code()
    Dim Newfile As String
    Dim x As Integer
    
    Sheets("Param").Select
    Newfile = Range("H2").Value     'H2 = C:\LSicom\Raw\03611\P06_0030.dat
    
    Sheets("P06_0030.csv").Select
    
    x = 0
    While Dir(Newfile) = ""
        Application.Wait (Now + TimeValue("0:00:01"))
        x = x + 1
        If x > 2 Then
            MsgBox "Could not find this file: " & Newfile
            Exit Sub
        End If
    Wend
    
    Workbooks.OpenText Filename:=Newfile
    
    End Sub

Posting Permissions

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