Help! Filename Checking Routine
Posted by Richard Winfield on December 06, 2001 8:35 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) . I was wondering if there might be a macro or an addition to my current macro that could check the file name against the exiting filenames in the folder and request the user to input a different filename to save as if a duplicate filename is found. The current macro I am using is:
Sub
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value
ActiveSheet.SaveAs Filename:="C:\packing lists\" & ThisFile & ".xls"
ActiveWorkbook.Close
End Sub
Thanks in advance for any help you may be able to provide :)
Posted by Juan Pablo G. on December 06, 2001 9:31 AM
I think this will work
If Dir("C:\packing lists\" & ThisFile & ".xls")<>"" then
MsgBox "File Already Exists !!!"
Exit Sub:
End If
Juan Pablo G.
Posted by Richard Winfield on December 06, 2001 10:57 AM
Looks like it might, but I didn't realize that I would get an Excel error message if the macro encountered a duplicate file. "A file ****.xls already exists. Do you want to overwrite it?" If you answer NO then the macro comes up with a runtime error. I guess my question should have been "How do I enable the macro to continue running so that I can provide a message box instructing the operator what do do next."
