VBA excel Application.getsaveasfilename error 13

Question

I'm trying to create a macro to have the user save a backup of the workbook to a specific place. I tried my code below but got an error 13 message. I don't need it to be saved as a macro-enabled workbook, but I thought that would be easier.

Sub openSaveDialog()
'
' gives error 13 message when clicking save
'
Dim saveSuccess As Boolean
Dim fNameRec As String
Dim dateNow As String
Dim saveToDir As String
saveToDir = "Z:\location of save\Old Archive spreadsheets\"
dateNow = Format(Now(), "mmddyyyy")
fNameRec = saveToDir & "BinderArchiveBackup_" & dateNow
Sheets(3).Range("E25") = fNameRec
'check if backed up today
If (Sheets(3).Range("E22") = Date) Then
    MsgBox "backup already saved today no need to save again"
    Exit Sub
End If

'open save as window
saveSuccess = Application.GetSaveAsFilename(InitialFileName:=fNameRec, FileFilter:= _
"Excel Files (*.xlsx)," & "*.xlsx, Macro Enabled" & _
"Workbook (*.xlsm), *xlsm")
'if backup saved, update date of last backup
If saveSuccess Then
    Sheets(3).Range "E22" = Date
    MsgBox "save successful"
End If
'if backup not saved, inform user
If Not saveSuccess Then
    MsgBox "save canceled, please save backup before adding new items to the archive today"
End If
End Sub

Things I tried tweaking

File filter to just macro enabled
File filter to just excel workbook
Blank file filter saving as type all files
Blank file filter with .xlsx at the end of the name
initial filename without directory but with ChDir so it opens in the right save location anyway

Any help would be great.

Save as window that opens

[1]

YowE3K YowE3K · Accepted Answer · 2017-07-05T20:46:36

GetSaveAsFilename returns a Variant, which will be a boolean False if the user cancelled the SaveAs dialog, or a string containing the filename that they chose if they didn't cancel the dialog.

Your line saying

Dim saveSuccess As Boolean

will cause an issue if a non-boolean value is returned. So use

Dim saveSuccess As Variant

instead.

This will still leave you with other problems though:

Sheets(3).Range "E22" = Date is invalid, and is probably meant to be Sheets(3).Range("E22") = Date
*xlsm should probably be *.xlsm

At no point are you actually saving the file. Your final bits of code should probably be something like:

If saveSuccess = False Then
    'if backup not saved, inform user
    MsgBox "save canceled, please save backup before adding new items to the archive today"
Else
    If UCase(Right(saveSuccess, 5)) = ".XLSM" Then
        ActiveWorkbook.SaveAs saveSuccess, xlOpenXMLWorkbookMacroEnabled
        'if backup saved, update date of last backup
        Sheets(3).Range("E22") = Date
        MsgBox "save successful"
    ElseIf UCase(Right(saveSuccess, 5)) = ".XLSX" Then
        ActiveWorkbook.SaveAs saveSuccess, xlOpenXMLWorkbook
        'if backup saved, update date of last backup
        Sheets(3).Range("E22") = Date
        MsgBox "save successful"
    Else
        MsgBox "Unrecognised file extension chosen - backup not created"
    End If
End If

VBA excel Application.getsaveasfilename error 13

1 Answers