Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"On error" within While loop failing
OK, so I know using an "On error" within a While loop isn't terribly
kosher but I'm interfacing Excel with a realtime data system and the existence/timing/availability of the key file is unpredictable. So I have used the following VBA code: ' Work off copy to avoid file collisions On Error GoTo againAgain: bTryAgain = True While bTryAgain = True FileCopy sFullSrcFileName, sFullFileCopyName bTryAgain = False GoTo notAgain againAgain: Call addLogMsg("openOrders file conflict: " & Now()) notAgain: Wend The issue is that the 'On error' works fine the first time the file isn't available. The log entry is made and the While loop restarts. What's fascinating, and deeply annoying, is that the second time through the loop execution halts at the "FileCopy". Simply halts with the line highlighted. No warning pop-up, no loop, no thing else -- though an Error 70 has been triggered. The intent of the code is to keep looping through the While loop until the FileCopy can succeed. I'm not at all clear why the 'On error' logic stops after just one error . Any ideas out there? = M = |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"On error" within While loop failing
I'd start by reading Chip Pearson's notes on error handling:
http://www.cpearson.com/Excel/ErrorHandling.htm Especially the stuff that starts with: "Enabled And Active Error Handlers" and continues for the remainder of that page. On 07/07/2010 11:33, Marchand wrote: OK, so I know using an "On error" within a While loop isn't terribly kosher but I'm interfacing Excel with a realtime data system and the existence/timing/availability of the key file is unpredictable. So I have used the following VBA code: ' Work off copy to avoid file collisions On Error GoTo againAgain: bTryAgain = True While bTryAgain = True FileCopy sFullSrcFileName, sFullFileCopyName bTryAgain = False GoTo notAgain againAgain: Call addLogMsg("openOrders file conflict: "& Now()) notAgain: Wend The issue is that the 'On error' works fine the first time the file isn't available. The log entry is made and the While loop restarts. What's fascinating, and deeply annoying, is that the second time through the loop execution halts at the "FileCopy". Simply halts with the line highlighted. No warning pop-up, no loop, no thing else -- though an Error 70 has been triggered. The intent of the code is to keep looping through the While loop until the FileCopy can succeed. I'm not at all clear why the 'On error' logic stops after just one error . Any ideas out there? = M = -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"On error" within While loop failing
On Jul 7, 9:55*am, Dave Peterson wrote:
I'd start by reading Chip Pearson's notes on error handling:http://www.cpearson.com/Excel/ErrorHandling.htm Especially the stuff that starts with: "Enabled And Active Error Handlers" and continues for the remainder of that page. On 07/07/2010 11:33, Marchand wrote: OK, so I know using an "On error" *within a While loop isn't terribly kosher but I'm interfacing Excel with a realtime data system and the existence/timing/availability of the key file is unpredictable. So I have used the following VBA code: * * *' Work off copy to avoid file collisions * * *On Error GoTo againAgain: * * *bTryAgain = True * * *While bTryAgain = True * * * * *FileCopy sFullSrcFileName, sFullFileCopyName * * * * *bTryAgain = False * * * * *GoTo notAgain againAgain: * * * * *Call addLogMsg("openOrders file conflict: "& *Now()) notAgain: * * *Wend The issue is that the 'On error' works fine the first time the file isn't available. The log entry is made and the While loop restarts. What's fascinating, and deeply annoying, is that the second time through the loop execution halts at the "FileCopy". Simply halts with the line highlighted. No warning pop-up, no loop, no thing else -- though an Error 70 has been triggered. The intent of the code is to keep looping through the While loop until the FileCopy can succeed. I'm not at all clear why the 'On error' logic stops after just one error . Any ideas out there? = M = -- Dave Peterson Dave, Thank you for the link. It's just what I needed to update both my brain and the VBA code. = M = |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using counter "i" in for loop gives error | Excel Programming | |||
"Coult not find specified object" and "Path-File access error" messages | Excel Programming | |||
Question on determining "ROW" inside of a "For .. RANGE " loop | Excel Programming | |||
"Select" and "Activate" failing? | Excel Programming | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming |