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.")
Exit Sub
End If
Set Filename = Range("FILENAME")
ActiveWorkbook.SaveAs Filename:=Filename, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

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,

-Ben

Posted by Dave Hawley on May 03, 2001 1:34 AM

Hi Ben

This should get you on your way!

Private Sub SaveAsFileName()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

Dim sFilename As String, sNewFilePath As String
Dim FileSearch, FileExists
Dim Reply As Integer

On Error Resume Next
'Parse file name and path to String Variable
sFilename = Range("FILENAME")
If sFilename = "" Then 'Error value or empty
MsgBox "Please enter your name and select a pay period before saving the timesheet" _
, vbOKOnly, "Name and Pay Period Required."
Exit Sub
End If
'Create Scripting.FileSystemObject
Set FileSearch = CreateObject("Scripting.FileSystemObject")
'Set FileExists to FileSystemObject
Set FileExists = FileSearch.GetFile(sFilename)

If IsEmpty(FileExists) Then 'No such path
Reply = MsgBox(sFilename & " contains an invalid path," _
& "Do you wish to create it", vbYesNo)
If Reply = vbNo Then Exit Sub
End If
'Get User to remove the File name
sNewFilePath = InputBox("Remove File name ONLY!", , sFilename)
'Create the new path
FileSearch.CreateFolder (sNewFilePath)
On Error GoTo 0
ActiveWorkbook.SaveAs Filename:=Filename

End Sub

Dave

OzGrid Business Applications

Posted by Ben O. on May 03, 2001 8:28 AM

Thanks Dave...

I appreciate it, Dave. The macro works great. That was an innovative way to create the directory when all you had was the filename. I should have mentioned that you could get just the path from Range("PATH"). :)

Thanks again for your help,

-Ben