Using Error handling within a Loop

I had posted this already in another thread but realised after further debugging that the problems in my code were multiple so am starting a fresh thread.Link to the original post is given below. My requirement is this
1. The most recent trading data is posted as a single zip file on an online resource. The file name is in the format "eq290413_csv.zip" where the numbers are the date of the trading day that data pertains to in ddmmyy format. No data is published on non-trading days.
2. I need to download this data and update my excel sheet. As the mane of the file is dynamic, I use a loop to start with the current date and iterate down one day at a time till I find a matching file to download.
3. I need to unzip this file and extract the csv file.
4.I need to update my excel sheet with the data in the CSV file.

The code works fine if the current file is the same as the current date or one day old. If older I get a run-time error 1004 with the msg "excel cannot find the text file to refresh this external data range". During Debugging the problems that I encountered in the code are as given below

1. I expect the code "iRet = URLDownloadToFile(0, StrURL, strPath, 0, 0)" to return a value of 0 only if the required zip file is available on the online resource and therefore the loop should continue at this level till the correct file is downloaded. This doesn't happen and a dummy zip file with no data is created even when there is no such file available at the online resource. Can anybody help so that the looping can occur at this stage.

2. In order to circumvent I added the "On Error GoTo" error handler to bring the execution back to the loop when the extraction of the zipped file returns an error. However during the debugging process I noticed that there seemed to be no error generated at this stage also and the execution moves out of the loop without having downloaded a valid file.

3. In the updating of the worksheet step, during the first iteration the error handler takes it to the error handling label and the loop is restarted. However on the second iteration a run-time error 1004 with the msg "excel cannot find the text file to refresh this external data range" is generated and the error handler does not take it to the error handling label. The break point is at the ".Refresh BackgroundQuery:=False" code line of the DoFileQuery Function.

Any help on why this unexpected behaviour would be helpful. I would like the loop to finish at the file download level so that once the correct zip file is downloaded the rest of the extraction and updating the worksheet part of the code need run only once.

Please Login or Register  to view this content.

http://www.excelforum.com/excel-prog...in-a-loop.html

Thank you oppressed. I have managed to get the code running by changing the <GoTo Reloop:> in the <Download Error:> Label to <Resume Reloop:> It was probably that after the first On Error GoTo statement was activated, the code continued to be in an unresolved error state and hence the error in the next iteration was not handled.
It however takes a long time as it goes through the process of download, unzip and trying to import from the dummy CSV files before the error handler captures the error and it goes back to reloop for the next iteration. Is there anyway that I can loop if the file that I am trying to download is not available at the online resource. I don't understand how a dummy file is created if no such file exists online. I am reposting the loop code as I originally conceived it which if I can get to work would speed up and unclutter the code. What I want is if the current date is Apr 29 and if the file on the online resource is of Apr 26 (eq260413_CSV.zip), then my loop should go through 4 iterations (3 no file msgs and one file downloaded msg) and download the file eq260413_CSV.zip. Whereas in the current form the <URLDownloadToFile> function just creates a dummy file eq290413_CSV.zip with no data in the first pass and returns a value of 0 to iRet whereby exiting the loop. Any thoughts....

Please Login or Register  to view this content.

Found the solution to my problem posting the link...
http://stackoverflow.com/questions/1...=votes#tab-top