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:
Application.ScreenUpdating = False
ThisFile = Range("d8").Value
ActiveSheet.SaveAs Filename:="C:\packing lists\" & ThisFile & ".xls"
Application.ScreenUpdating = True
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 ;)