Help With Error Handling (1 Viewer)

[J3nny]

Hi!

I have ventured into VBA for MS Access and am regretting it already. I've got a macro which deletes sheets in an excel spreadsheet and then exports data to excel, then kicks of a macro within the excel worksheet which opens another spreadsheet, saves the file and does some other manipulation. This is all working, after many many run-time errors. However, sometimes when this runs it crashes Access right after deleting the excel worksheets and before the export. It does this a bit randomly, I've found that sometimes there's a hidden excel session still running which I think causes the issue but even if I kill that first sometimes it still falls over.

This would be fine as I can just run the macro again after restarting access and it usually completes the second time, except that the macro fails on the commands to delete the excel sheets, which were deleted by the first macro run.

Very long story short, i need some error handling which when it encounters this line:

objExcel.Sheets("Report").Delete

if it results in an error (as the sheet doesn't exist) it ignores the command and moves onto the next line. I've tried a few ways but can't figure this out, could anyone help please? Thank you!

Jenny

 

[pbaldy]

The basics of error handling:

http://www.baldyweb.com/ErrorTrap.htm

In this instance, you can trap for that particular error number and use

Resume Next

in the Case for that number so code goes back to where it left off. Another method would be to put

On Error Resume Next

right before that line and then

On Error GoTo ErrorHandler

right after so normal error handling is resumed.

 

[J3nny]

Brilliant, thank you pbaldy, I'll give it a go now and no doubt be back in 10 minutes when I can't get it to work..

 

[pbaldy]

Happy to help!