VBA to display Save As Dialog Box

[srdavisgb]

Thank you in advance for your assistance.

I have written a macro to copy a worksheet to a new workbook. I have identified a file name and I prompt the user to password protect the file. It appears the company blocks some users from saving to the C:\Temp\ drive. I think they add the User ID in the path (e.g., C:\Temp\KZ4XWS\). To avoid this issue, I would like to prompt the user with the Save As dialog box so they can select a save location.

How to I prompt for a Save Dialog box for location and populate the file name and password?


Below is my code:

Dim ESC_Doc_Name As String
Dim Password As String
Dim ErrorNumber As Integer

ESC_Doc_Name = Sheets(1).Cells(5, 2)
Password = Sheets(1).Cells(9, 2)

'Check to ensure a valid sheet name is entered
If ESC_Doc_Name = "" Or ErrorNumber = "13" Then
MsgBox "You must enter the Employee's name exactly as it is displayed on the tab in this Workbook. Please enter the Employee's name.", , "Enter ECS Name Reminder"
Exit Sub
End If

'Check to ensure a "PASSWORD" is entered
If Password = "" Or ErrorNumber = "13" Then
MsgBox "You must enter a password to create this file. Please enter a password of up to 8 characters in length.", , "Enter Password Reminder"
Exit Sub
End If


Sheets(ESC_Doc_Name).Select
Sheets(ESC_Doc_Name).Copy
Application.Dialogs(xlDialogSaveAs).Show
'ActiveWorkbook.SaveAs Filename:="C:\Temp\" & ESC_Doc_Name & "_ECS.xlsx", FileFormat _
':=xlOpenXMLWorkbook, Password:=Password, WriteResPassword:="", ReadOnlyRecommended:=False _
', CreateBackup:=False


'ActiveWorkbook.SaveAs Filename:="C:\Temp\" & ESC_Doc_Name & "_ECS.xlsx", FileFormat:= _
' :=xlOpenXMLWorkbook, Password:=Password, WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False
ActiveWorkbook.Close


Sheets("Macro").Select
Range("B5").ClearContents
Range("B9").ClearContents
ActiveWindow.SelectedSheets.Visible = False

MsgBox "Your new file has been saved to C:\TEMP\ directory.", , "File Location"


End Sub


Again, thanks for your assistance.

 

[JoeMo]

This line will open the SaveAs dialog box.

Application.Dialogs(xlDialogSaveAs).Show


If you have already named the activeworkbook, it's name will populate the box. The dialog box accepts several arguments:
document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec

 

[srdavisgb]

I guess I don't know how to name the activeworkbook without saving it. Currently, my code saves the workbook and then displays the xlDialogSaveAs dialog box. How do I name the workbook to get the filename pre-populated in the dialog box. Below is my code:

ActiveWorkbook.SaveAs Filename:=ESC_Doc_Name & "_ECS.xlsx", FileFormat _
:=xlOpenXMLWorkbook, Password:=Lname & "2015", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.Dialogs(xlDialogSaveAs).Show

Application.Dialogs(xlDialogSaveAs).Show

'ActiveWorkbook.SaveAs Filename:="C:\Temp\" & ESC_Doc_Name & "_ECS.xlsx", FileFormat:= _
' :=xlOpenXMLWorkbook, Password:=Password, WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False

ActiveWorkbook.Close

Again, thanks for your help.

 

[JoeMo]

Since you have already saved the file before you open the SaveAs dialog box, the file name should populate the box.