Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: VBA File Open Question

    My Excel VBA application uses a batch file and Winzip to write into a text file a list of the contents of a zipped file. From this text file, I verify that the one file inside the zipped file has the expected file name and was zipped less that 30 days prior to the current date. This works, but here's the problem.

    Between writing the text file and then opening it, Excel does not find the text file unless I put a delay in between these two actions. So adding a message box to interupt the program after the file has been written allows Excel VBA to open the text file after the message box has been cleared. It may be that, even though I've closed the text file, VBA may be trying to read it before it gets closed and can't find it because it is still open.

    Anyone have any suggestions on how to write a text file and fully close it before opening it in Excel VBA?

    Thanks.
    Jerry

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    It is unclear how you are writing the text file, is this with VBA or is some other 3rd party routine doing it.

    If its done by VBA, then why not scan the contents as it is written to find the file(s) that you want to open and remeber it/them for openeing later (do you actualy need the text file)?

    You can always impose a delay in VBA if you want unattended execution!

    Sorry there seems to be more questions than answers.


    MTB

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Coding is simplified for this discussion. c:\Temp is not the actual path.
    VBA code executes the following batch file:
    @Echo Off
    "c:\Program Files\Winzip\WZUnzip.exe" -v c:\Temp\Test.zip > c:\Temp > ziplist.txt
    Exit

    Here's the VBA code:
    HTML Code:
    myBatFileName = "C:\Temp\ZListBat.bat"
    Call Shell(myBatFileName, 1)
    Workbooks.OpenText fileName:= _
            "C:\Temp\ziplist.txt", Origin _
            :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
            , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
            False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(1, 1) _
            , TrailingMinusNumbers:=True
    The Workbooks.OpenText statement fails to find the text file unless there is a delay. The question is, what is a smart, efficient way to delay VBA until the text file closes?

    The purpose is to read the text file and extract the file date and file name to ensure that the expected file is found and it is current. It checks to see that the person responsible for providing the zipped file on a shared drive each month has done the job.

    Jerry

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    Thanks for the further explanation, Jerry.

    What about something like
    Code:
    Sub sample()
    
    Const lLoopMaximum As Long = 10 'adjust to suit
    Dim lLoopCount As Long
    
    lLoopCount = 1
    
    Do
        On Error Resume Next
            'Try to open the text file
            Workbooks.OpenText ....
            
            'Err will still be zero if file opens OK
            If Err = 0 Then Exit Do
        Err.Clear
        lLoopCount = lLoopCount + 1
        If lLoopCount = lLoopMaximum Then GoTo Error_Handler_File_Not_Opened
            
    Loop
    
    'Reset error handling
    On Error GoTo 0
    
    'Processing of opened text file
    
    
    
    Exit Sub
    
    Error_Handler_File_Not_Opened:
        'Code to handle file not opened within lLoopMaximum tests
    
    End Sub
    OK?

    It ended up more complicated than I expected, so I guess it can be simplified.

    Fazza

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thank you all for your tips. Fazza, your solution may not work, to continually try opening the text file in a loop, and I don't know why this works in two different ways, but sometimes I get the error that the text file can't be found and sometimes the file opens but is blank, then after I close the blank file, a dialog box pops up saying that the text file is ready.

    Here is the solution to create a delay that works:
    HTML Code:
    'delay the procedure for 2 seconds to give ziplist.txt time to be closed
    Application.Wait Now + TimeSerial(0, 0, 2)
    2 seconds delay between running a batch file that creates the text file and opening the text file in Excel VBA has worked so far. It did not succeed every time with a 1 second delay.

    This one is solved. Thanks again.
    Jerry

  6. #6
    Join Date
    Feb 2006
    Posts
    113
    Good work, Jerry.

    The simple delay looks a neater solution, and as you say, the loop might not even work. I wonder too if using DoEvents might be suitable...

    regards,
    Fazza

Posting Permissions

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