Need thorough error handling on Save As macro
Posted by Ben O. on May 02, 2001 10:13 AM
I need a macro that saves the active workbook as a specific name in a specific directory. The name and path come from a range in the workbook called FILENAME. Here's what the macro needs to do:
- If Range("FILENAME") is #N/A, display an alert and exit the macro.
- If the file path doesn't exist, display a vbYesNo box that creates the path if the user clicks on yes, exits the macro if he clicks on no.
- If the path can't be created (due to a lack of write-access), display an alert and exit the macro.
- If a file already exists with the specified name in the specified directory, display a vbYesNo asking whether to overwrite or not.
Here's what I have so far:
Private Sub SaveAsFileName()
If Range("FILENAME") = "N/A" Then
x = MsgBox("Please enter your name and select a pay period before saving the timesheet", vbOKOnly, "Name and Pay Period Required.")
Set Filename = Range("FILENAME")
ActiveWorkbook.SaveAs Filename:=Filename, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
It only works if the path exists and there's not already a file there with the specified name. If my FILENAME range is N/A, the macro errors out (type mismatch).
I'd appreciate any help. Thanks in advance,