Forcing File Name with xlDialogSaveAs.Show?

[tbakbradley]

I have a macro (simplified it here) that performs many activities, but then presents the User with the Save As Dialog Box to save the file. Once the file is saved, additional steps happen. I wanted a method to stop the Macro if the User Canceled the Save As because if not, the current form would be emailed to another team without the proper data on the current saved file.

I now have a need to Force a File Name. I will need to set up some Variables based on Cells in the Spreadsheet. File name would have to say be "Case Number_Service" where both of those are stored in the spreadsheet as they change per Request.

I'm having difficulties since I had to prevent the macro from moving forward if the User canceled the Save As. Is there anything I can do with this code to force a file to be saved with a specific name based on variables (user would select path)?

Sub Build ()
 
Application.EnableEvents = False  'turn events off so the SaveAs Private Sub for Workbook is turned off and not to hinder Save As in this Macro
    'Save Feature will end if Engineer clicks Cancel
    [COLOR=#ff0000]X = Application.Dialogs(xlDialogSaveAs).Show
    If X = False Then[/COLOR]
MsgBox "You Canceled your save. Therefore, this step cannot move forward or the Auto Emails would be sent with the incorrect Attachment.”
    Sheets("Sheet 1").Select
    Range("Y3").Select
    Exit Sub
    End If
  Application.EnableEvents = True 'turn events back on - this is critical, otherwise no events will fire again
    Sheets("Sheet 2").Select
    Range("Z1").Select
   EmailOptions.Show
End Sub

 

[Scott Huish]

Don't use Dialog, use Application.GetSaveAsFileName, here is an example:

Sub test()
Dim X
X = Application.GetSaveAsFilename("Case Number_Service", "Excel Files (*.xls), *.xls")
If X = False Then '
'Your Code
End If

End Sub

 

[tbakbradley]

Don't use Dialog, use Application.GetSaveAsFileName, here is an example:

Sub test()
Dim X
X = Application.GetSaveAsFilename("Case Number_Service", "Excel Files (*.xls), *.xls")
If X = False Then '
'Your Code
End If

End Sub

That worked perfectly. I was able to set up my variables for the File name as well with this. I tried the GetSaveAsFileName before, but I must not have been formatting it correctly as I had problems until I tried this.

 

[tbakbradley]

Don't use Dialog, use Application.GetSaveAsFileName, here is an example:

Sub test()
Dim X
X = Application.GetSaveAsFilename("Case Number_Service", "Excel Files (*.xls), *.xls")
If X = False Then '
'Your Code
End If

End Sub

I thought this was working, but just realized it is not. I am correctly receiving the Save As box with my File Name Auto Populated based on the Variables I declared and defined. However, when I choose a Folder and click save to move on to the rest of the Macro, I have realized the file never saved. When I used the simple xlDialogSaveAs.Show, to where the user typed in their own file name, it saved fine. Any idea why everything is working by receiving the Save As box, with my file name and file type (xlsm) ready to go, but when I click Save, it doesn't save?

Sub Data_Build()Dim CN As String
Dim SR As String
CN = Sheet22.Range("Cover_CaseNumber").Value
SR = Sheet22.Range("Cover_Service").Value


If Sheets("Inquiry").Range("FTTIGCNEEng") = "**********" Then
    MsgBox "You must select the User assigned to this case to continue.  This will be used for the CC of the auto email", vbExclamation, "User not Selected"
    Exit Sub
    End If


Application.EnableEvents = False  'turn events off so the SaveAs Private Sub for Workbook is turned off and not to hinder Save As in this Macro


ActiveSheet.Range("Details").Columns.Hidden = False


    Sheets("Data").Visible = True
    Sheets("Data").Select
    Range("A1").Select
  
    'Save Feature will end if Engineer clicks Cancel
    MsgBox "File Name is Auto Populated with CaseNumber_Service.  If something happens and that is removed before you save the file, you must save in that format.", VbExlamation, "Standard File Format- CaseNumber_Service"
[COLOR=#ff0000]    X = Application.GetSaveAsFilename(CN & "_" & SR, "Excel Files (*.xlsm), *.xlsm")[/COLOR]
    If X = False Then
    MsgBox "You Canceled your save. Therefore, this step cannot move forward or the Auto Emails would be sent with the incorrect Attachment."
    Sheets("Inquiry").Select
    Range("Y3").Select
    Exit Sub
    End If    
   
    
    
    Sheets("Inquiry").Select
    Range("Z1").Select
   
    
   EmailOptions.Show
   
  Application.EnableEvents = True


End Sub

 

[Scott Huish]

You have to have code to save it with the path and filename returned from GetSaveAsFilename. That's all it does is get the SaveAs Filename, you have to tell it what to do with it.


For example:

X = Application.GetSaveAsFilename(CN & "_" & SR, "Excel Files (*.xlsm), *.xlsm")
ThisWorkbook.SaveAs X

 

[tbakbradley]

You have to have code to save it with the path and filename returned from GetSaveAsFilename. That's all it does is get the SaveAs Filename, you have to tell it what to do with it.


For example:

X = Application.GetSaveAsFilename(CN & "_" & SR, "Excel Files (*.xlsm), *.xlsm")
ThisWorkbook.SaveAs X

Wow. I looked over the same code so many times, and completely missed that. Thank you!!!!