MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel message crashes macro

Posted by Richard Winfield on December 07, 2001 9:21 AM

I have a current macro that saves the active worksheet to a new workbook with a single sheet, then saves that workbook with a filename taken from a cell on the worksheet. I am using Excel to create packing lists for shipments .The problem that I have is that occasionally I will have multiple packing lists created from the same sales order (which is the value that I must use as a file name) . The current macro I am using is:

Sub
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value
ActiveSheet.SaveAs Filename:="C:\packing lists\" & ThisFile & ".xls"
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub


This works great except I get an Excel message if the macro encounters a duplicate filename. "A file ****.xls already exists. Do you want to overwrite it?" If you answer "NO" then the macro comes up with a runtime error. My question is: How do I enable the macro to continue running so that I can provide a message box instructing the operator what do do next? Probably a simple answer, but I am a bit of a newbie at Excel ;)

Posted by BenH on December 07, 2001 1:25 PM

You need to insert some basic error-handling. What is happening is that Excel is automatically prompting the user to confirm that they want to overwrite the existing workbook. If they answer "no", Excel cannot complete the SaveAs command and the macro produces an error. Here is one simple solution....

Sub
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value
On Error Goto do_not_overwrite ' Tells Excel where to go if an error occurs
ActiveSheet.SaveAs Filename:="C:\packing lists\" & ThisFile & ".xls"
On Error Goto 0 ' Restores normal error-handling
ActiveWorkbook.Close
Application.ScreenUpdating = True
Exit Sub
do_not_overwrite:
MsgBox "MessageToUser"
End Sub

Posted by Richard Winfield on December 07, 2001 2:09 PM


Thank you !! Works perfectly. Just the simple solution I was looking for.

Rick