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?
Coding is simplified for this discussion. c:\Temp is not the actual path.
VBA code executes the following batch file:
"c:\Program Files\Winzip\WZUnzip.exe" -v c:\Temp\Test.zip > c:\Temp > ziplist.txt
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.
Const lLoopMaximum As Long = 10 'adjust to suit
Dim lLoopCount As Long
lLoopCount = 1
On Error Resume Next
'Try to open the text file
'Err will still be zero if file opens OK
If Err = 0 Then Exit Do
lLoopCount = lLoopCount + 1
If lLoopCount = lLoopMaximum Then GoTo Error_Handler_File_Not_Opened
'Reset error handling
On Error GoTo 0
'Processing of opened text file
'Code to handle file not opened within lLoopMaximum tests
It ended up more complicated than I expected, so I guess it can be simplified.
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:
'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.