Errors when Doing Thousands of Workbook.Save / Workbook.Open

[Ceeyee]

I have a program that requires a lot of Workbook.Save and Workbook.Open operations through out the day.

Most of time the program works ok, but once or twice a week, it stops with "fail to open / save object" errors on the Workbook.Save or Workbook.Open line for no obvious reasons (such as windows 7 thinks the file is already opened when it's not).
Clicking "Debug" and the "Play" button usually allows the program to work again, as the program will then successfully open/save the workbooks.

This is not what I want, because it causes interruption and requires manual operations.


I tried the following code using error handling to do the file operations five times before reporting errors, but it didn't work:

Application.DisplayAlerts = False
For numErrTry = 0 To 5
If numErrTry = 5 Then
On Error GoTo 0
Else
On Error Resume Next
End If


The WB.Save or WB.Open Line here


If Err.Number = 0 Then
Exit For
Else
Sleep 5000
End If

Next numErrTry
On Error GoTo 0
Application.DisplayAlerts = True


Are there other solutions to these occasion errors when doing workbook.save/open operations?
Thanks.

 

[Jeffrey Mahoney]

It's probably due to using too many resources. You may have to set Objects to nothing. If your SUBS get nested calls without releasing the calling SUB, this could cause the problem. Are you closing the workbooks before opening a new one?

 

[Ceeyee]

It's probably due to using too many resources. You may have to set Objects to nothing. If your SUBS get nested calls without releasing the calling SUB, this could cause the problem. Are you closing the workbooks before opening a new one?

I haven't tried setting Objects to nothing after closing the workbook, because the closing method is usually in last few lines in a sub. And yes, I close workbooks before opening new ones, but the system still finds conflicts when this is done too many times.

Let me try it. Thanks.

 

[Jeffrey Mahoney]

If you try to trap an error and the code just quits and forces an Excel Crash or a workbook crash then you have resource issues

If you get an error code, then it's probably something else. You need to something to tell you what the error is so you can investigate. I turned off the first line and added a msgbox to see the error

'  Application.DisplayAlerts = False
  For numErrTry = 0 To 5
    If numErrTry = 5 Then
      On Error GoTo 0
    Else
      On Error Resume Next
    End If
  
  
  'The WB.Save or WB.Open Line here
  
  
    If Err.Number = 0 Then
      Exit For
    Else
      MsgBox "Error Number: " & Err.Number
      Sleep 5000
    End If
    
  Next numErrTry
  On Error GoTo 0
  Application.DisplayAlerts = True

 

[Ceeyee]

If you try to trap an error and the code just quits and forces an Excel Crash or a workbook crash then you have resource issues

If you get an error code, then it's probably something else. You need to something to tell you what the error is so you can investigate. I turned off the first line and added a msgbox to see the error

'  Application.DisplayAlerts = False
  For numErrTry = 0 To 5
    If numErrTry = 5 Then
      On Error GoTo 0
    Else
      On Error Resume Next
    End If
  
  
  'The WB.Save or WB.Open Line here
  
  
    If Err.Number = 0 Then
      Exit For
    Else
      MsgBox "Error Number: " & Err.Number
      Sleep 5000
    End If
    
  Next numErrTry
  On Error GoTo 0
  Application.DisplayAlerts = True

Thank you.