How do I display a Save As dialog box in Excel 2007 VBA with a default filename ?

You can use Application.GetSaveAsFilename.

In the attached file enter the default/initial filename in E6 and the path in D6.
EE---SaveDialogEx.xlsm

Thanks for that.

I'm assuming to actually save the file I'll need the following code after the GetSaveAsFilename ?
For example :
    With wbRequests
        .SaveAs Filename:=strDefaultFile
    End With

What code will I need to prompt for a file overwrite of an existing file ?
Thanks

Yes, GetSaveAsFileName only gets the filename.

So you need code to save, but using varFileName not strDefaultFile.

Not sure what you mean about overwriting.

Do you want to inform the user that there is already a file with that name and ask them what they want to do?

Something like this which asks the user if they want to overwrite.

If they click Yes the file will be overwritten, if the click No the code is exited.

Private Sub CommandButton1_Click()
Dim strDefaultFile As String
Dim strPath As String
Dim strFileFilter As String
Dim resp
Dim varFileName

    strPath = Worksheets("Sheet1").Range("D6").Value

    If strPath <> "" Then
        If Len(Dir(strPath, vbDirectory)) <> 0 Then
            ChDir strPath
        End If

    End If

    strDefaultFile = Worksheets("Sheet1").Range("E6").Value

    strFileFilter = "Excel Files (*.xlsm;*.xls), *.xlsm;*.xlsm"

    varFileName = Application.GetSaveAsFilename(strDefaultFile, strFileFilter, , "Enter filename:")

    If TypeName(varFileName) = "String" Then
        MsgBox "Filename to save with: " & varFileName

        If Len(Dir(varFileName)) > 0 Then
            resp = MsgBox("File" & varFileName & " already exists." & vbCrLf & vbCrLf & "Do you wish to overwrite?", _
                          vbYesNo + vbCritical, "Overwrite file")
                          
            If resp = vbYes Then
            
                Application.DisplayAlerts = False
            Else
                Exit Sub
            End If
            
        End If
        Set wbRequests = Workbooks.Add
        
        wbRequests.SaveAs Filename:=varFileName

        Application.DisplayAlerts = True

    Else
        MsgBox "Cancel pressed"
    End If
    
End Sub

Select allOpen in new window

Before your code I'm building the filename to include the Windows User name...............

   ' Format the name of the workbook
   UName = Environ("USERNAME")
   newFilename = "Requests " & Format$(Date, "yyyy-mm-dd") & " " & UName & ".xlsm"

I then set your variable to this filename........

    strDefaultFile = newFilename

When I run the procedure, the Save File as dialog box is displayed but there is no filename in the File Name box. And the Save as Type dropdown only shows 'Excel Files'

When I click Save, it gives me a runtime error 1004 - "This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as Type "

Any ideas ?

Changing strFileFilter should fix the first part:

strFileFilter = "Excel Files (*.xlsm), *.xlsm"

and for the save error:

 wbRequests.SaveAs Filename:=varFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Template-to-EE-v1.xlsm

Hi,

I've attached a cut-down version of the application with your code linked to a Save button on the Menu sheet. The requirement is straightforward :

1. Click on the button.
2. Open the Save As dialog box with a default name in the File Name box.
3. Click Save.
4. Prompt the user (if the file already exists) to overwrite the existing file, or cancel.

Thanks
Toco

Yes, got there eventually. Thanks for your patience.
Toco

Here's your file updated to deal with default file and save problem.
EE---Template-V2.xlsm