VBA using a timer to trap errors

[jscranton]

I have a long piece of code (not worth posting in full) that loops through a folder of excels and performs 7 different procedures on them. The issue is that a given workbook will sometimes encounter issues (usually related to the internal structure or size of the workbook) which can cause the main module to error out. I was hoping to run a timer at the outset of the loop that once a certain time elapsed (say 2 minutes) it would move the excel to an error folder.

I think it would look something like this but I am not sure how to use the procedure call of the Application.OnTime method

  myFile = Dir(inProcessingPath & myExtension)


  Do While myFile <> ""


      ErrorTime = Now() + TimeValue("00:02:30")
      Application.OnTime ErrorTime, 'Do something here I don't understand
      
      Set Wb = Workbooks.Open(fileName:=myPath & myFile, UpdateLinks:=False, Editable:=True)


        'here is where the 7 procedures are run


'Save and Close Workbook
      Wb.Save
      Set FSo = CreateObject("scripting.filesystemobject")
      FSo.MoveFile source:=Wb.path, Destination:=newPath 

'But if timer elapase newPath = path to an error Folder
  
'Get next file name
      myFile = Dir





  Loop

 

[Jerry Sullivan]

The approach of using a timer to determine whether an error might have occurred seems overly complicated to me.

What Error Handling (using On Error ... statements), if any, are you using in your code now (including the called procedures)?

It would be simpler and more robust to have a general error handler that triggers that moving of the file(s) that generate unexpected errors.

 

[jscranton]

Jerry,

Thanks for the reply. I have several error traps which are mostly of the On Error Goto variety. However, the real issue issue is that this code is deployed over network folders and when it encounters a very large excel (or one with robust internal protection beyond what a binary password can crack) it often crashes the main module. My hope was to set those aside so they could be dealt with in a one-off case-by-case method.

Happy to hear your thoughts.

J

 

[Jerry Sullivan]

What exactly do you mean by "crashes"? If the Excel application closes or stops responding, the error handling you are trying to implement won't run.

 

[jscranton]

Jerry,

Thanks. Defining what I mean by "crashes" is exactly the problem I am trying to work around. The main module is working through, literally, tens and thousands of excel created by various users. I simply can't know what is in each workbook as it will constantly change. So, I am trying to think of a way to handle the types of files.

In plainer language, folder A contains 1000 excels. Main workbook opens Workbook 348 of 1000 and starts to run procedures 1-7. For some reason (which I won't always know), Main workbook encounters a problem with Workbook 348. After some period of time, I want the Main Workbook to stop trying to run procedures 1-7, close the workbook without making any changes, move it to a folder (so I can handle it later) and continue to workbook 349 of 1000.

Does that help?

Thanks,

J

 

[Jerry Sullivan]

J, It might be possible to create a way for the main program to exit an opened workbook that has "stalled" but the application isn't actually closing or not responding, however I think that's a much more complicated method than just improving your code and error handling so that doesn't happen.

It seems like you believe that's not possible due to the uncertainty of the contents of the many files that need to be processed, but good coding and error handling should allow you to do that.

If you can post an example of where the code has failed in an unforeseen condition, I can try to suggest error handling that would allow the error to be recorded and for processing to continue.