Results 1 to 5 of 5

Thread: Save As Macro, Error on Cancel

  1. #1

    Save As Macro, Error on Cancel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

  2. #2
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Try:

    Code:
    Sub SaveAs()
    
        Dim 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"
            If .Show = True Then
                For Each varFile In .SelectedItems
                    FileName = varFile & "\"
                Next
            Else
                Exit Sub
            End If
        End With
    
    
        ActiveWorkbook.SaveAs FileName & Iif(Right(Range("D4"),5) <> ".xlsm", Range("D4") & ".xlsm", Range("D4"))
    
    End Sub
    
    Last edited by millz; 2013-09-06 at 08:45 AM.

  3. #3
    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."

  4. #4
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    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

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •