We help IT Professionals succeed at work.

VBA Use Windows File Dialog Box to Create a Workbook

PJ0302917
PJ0302917 asked
on
Hi

I'm working on a VBA application and would like to let the user create a new Workbook and choose where to save it using the well know Windows Dialog boxes. I can find examples of msoFileDialogFilePicker so select a file or another method for selecting a folder but couldn't see anything to actually save a new Workbook. Could someone point me in the right direction if they know.

Thanks
Comment
Watch Question

Top Expert 2016

Commented:
Hi,

pls try
 Filename = Application.GetSaveAsFilename( _ 
    fileFilter:="Excel Files (*.xls), *.xl") 
    If Filename <> False Then 
        ActiveWorkbook.SaveAs Filename 
    End If 

Open in new window

Regards

Author

Commented:
Hi

Thanks for the help, this works. At the moment its saving the current Workbook and I need to create a new empty workbook but I'll see if I can get it working. Thanks for the help
Top Expert 2016
Commented:
then try
Sub Macro()
  Filename = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xl*), *.xl*")
    If Filename <> False Then
        Set wb = Workbooks.Add
        wb.SaveAs Filename
        'wb.Close
    End If

End Sub

Open in new window

Author

Commented:
That worked a treat, thank you Rgonzo