VBA File Open Question

JerryDal · #1 (**permalink**) 10-30-06, 19:55

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

MikeTheBike · #2 (**permalink**) 10-31-06, 08:06

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

JerryDal · #3 (**permalink**) 10-31-06, 12:01

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

Fazza · #4 (**permalink**) 10-31-06, 18:59

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

JerryDal · #5 (**permalink**) 10-31-06, 19:18

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

Fazza · #6 (**permalink**) 10-31-06, 19:23

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

Internet Services

Web Development

Digital Marketing

Consumer Tech