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

Tocogroup
Tocogroup used Ask the Experts™
on
Hi,

On the click of an Active X button on my Excel 2007 worksheet I would like the Save As dialog box to display and pass a default filename to save.
What is the code for this ?
Thanks
Toco
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You can use Application.GetSaveAsFilename.

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

Author

Commented:
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
NorieAnalyst Assistant

Commented:
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

Open in new window

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
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 ?
NorieAnalyst Assistant

Commented:
Changing strFileFilter should fix the first part:

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

and for the save error:

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

Author

Commented:
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
Analyst Assistant
Commented:
So it's solved then?

Author

Commented:
Yes, got there eventually. Thanks for your patience.
Toco
NorieAnalyst Assistant

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial