Save As Macro, Error on Cancel

B

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

I have a macro developed that saves a file in a desired location, but if the user presses cancel, the file is saved as "FALSE" in that location. I've tried (and failed) to end the sub on an incorrect name, but it's not working.

Please see below

Sub SaveAs()



Dim FileName As Variant
Range("d4").Select
FileName = ActiveCell.Value

If FileName = "False" Then Exit Sub

ActiveWorkbook.SaveAs FileName = Application.GetSaveAsFilename(FileName, filefilter:="Excel Files(*.xlsm),*.xlsm")

End Sub


Thank you for your time,
 
Try:

Code: 
[COLOR=#333333]Sub SaveAs()[/COLOR]

[COLOR=#333333]    Dim FileName, varFile As Variant
[/COLOR]    Dim fDialog As office.FileDialog
    
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)


    With fDialog
        .AllowMultiSelect = False
        .Title = "Please browse to where you would like to save"
        If .Show = True Then
            For Each varFile In .SelectedItems
                [COLOR=#333333]FileName [/COLOR]= varFile & "\"
            Next
        Else
            Exit Sub
        End If
    End With


[COLOR=#333333]    ActiveWorkbook.SaveAs FileName & Iif(Right(Range("D4"),5) <> ".xlsm", [/COLOR][COLOR=#333333]Range("D4") & ".xlsm", [/COLOR][COLOR=#333333]Range("D4"))[/COLOR]

[COLOR=#333333]End Sub
[/COLOR]
 
Last edited:
Thanks for the help. The code solves the cancel issue, but it is not finding the save location. Sorry, I should have explained on the front end. The cell selected contains a pathname that solves the save location for our file tree. The original will go to the rough saving location for the file, but on cancel, the file is saved as "FALSE."
 
Try this, this is part of a code that I use myself personally. It asks for the directory, followed by the desired file name. If it's too troublesome for you or your user, feel free to change to suit your needs
Code: 
Sub SaveAs()


    Dim FileDir, FileName, varFile As Variant
    Dim fDialog As Office.FileDialog
    
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)




    With fDialog
        .AllowMultiSelect = False
        .Title = "Please browse to where you would like to save"
        .InitialFileName = IIf(Right(Range("D4"), 1) <> "\", Range("D4") & "\", Range("D4"))
        If .Show = True Then
            For Each varFile In .SelectedItems
                FileDir = varFile & "\"
            Next
        Else
            Exit Sub
        End If
    End With
    
    FileName = Application.InputBox("Please enter a file name to be saved as: " & vbCrLf & "Note: .xlsm may be omitted", Title:="Enter file name to be saved as", Default:="MyWorkBook.xlsm")
    GoTo checkFileName
fileBlank:
    FileName = Application.InputBox("You did not enter a file name." & vbCrLf & vbCrLf & "Please enter a file name to be saved as: " & vbCrLf & "Note: .txt may be omitted", Title:="Enter file name to be saved as", Default:="MyWorkBook.xlsm")
    GoTo checkFileName
fileContainsSpecial:
    FileName = Application.InputBox("The file name may not contain any of these characters: " & vbCrLf & "\ / : * ? "" < > |" & vbCrLf & vbCrLf & "Please enter a different file name to be saved as: " & vbCrLf & "Note: .xlsm may be omitted", Title:="Enter file name to be saved as", Default:="MyWorkBook.xlsm")
checkFileName:
    If FileName = "False" Then Exit Sub
    If FileName = "" Then GoTo fileBlank
    If InStr(FileName, Chr(34)) > 0 Or InStr(FileName, "\") > 0 Or InStr(FileName, "/") > 0 Or InStr(FileName, ":") > 0 Or InStr(FileName, "*") > 0 Or InStr(FileName, "?") > 0 Or InStr(FileName, "<") > 0 Or InStr(FileName, ">") > 0 Or InStr(FileName, "|") > 0 Then GoTo fileContainsSpecial
    If Right(FileName, 4) <> ".xlsm" Then FileName = FileName & ".xlsm"


    ActiveWorkbook.SaveAs FileDir & FileName


End Sub
 
Thanks, that did the trick. On the second go I realized that our filenames have different formats (the inline iff was throwing mine off) so the original may have worked too. Thanks again.
 
You must log in or register to reply here.
Back
Top