Using Dialog box for opening and saving the workbook

 

In this article, we cover the code used for displaying workbook opening dialog box and save as dialog box.

Workbook attached in this article contains three macros:-

  1. OpenOneFile

  2. OpenMultipleFiles

  3. SaveFile

OpenOneFile macro is used for opening single workbook whereas OpenMultipleFiles macro is used for selecting and opening multiple workbooks at same time.

Both macros use GetOpenFilename method of Application object for opening Excel workbook.

SaveFile macro is used for opening save as dialog box and saving the workbook at the specified location.

Code explanation

OpenOneFile and OpenMultipleFiles Macros

Both macros use GetOpenFilename method of Application object for opening Excel workbook.

OpenOneFile Macro

ArrowOpeningSingle

OpenMultipleFiles Macro

ArrowOpeningMultiple

Application.GetOpenFilename Method

GetOpenFilename method of Application object is used for displaying the file opening dialog box and it returns the name of the file selected by the user.

Application .GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

FileFilter is an optional parameter that specifies the file filtering criteria.

FilterIndex is an optional parameter, it specifies the index number for default file filtering criteria.

Title is an optional parameter that specifies the title of the open dialog box.

ButtonText is an optional parameter that is used only in Macintosh.

MultiSelect is an optional parameter that carries Boolean values. If its value is TRUE then multiple file selection is allowed otherwise only single file can be selected.

SaveFile Macro

SaveFile Macro is using GetSaveAsFilename method of Application object for opening save as dialog box and assigning the file name, and selecting the location for saving the workbook.

ArrowSavingWorkbook

Application.GetSaveAsFilename Method

GetSaveAsFilename method is used for displaying the Save As dialog box. It returns the location and file name for saving the workbook.

Application .GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

InitialFilename is an optional parameter that specifies the suggested file name.

FileFilter is an optional parameter that specifies the file filtering criteria.

FilterIndex is an optional parameter, it specifies the index number for default file filtering criteria.

Title is an optional parameter that specifies the title of the Save As dialog box.

ButtonText is an optional parameter that is used only in Macintosh.

 

Please follow below for the code

Option Explicit

Sub OpenOneFile()

Dim FileName As Variant

'Displaying the open file dialog box
FileName = Application.GetOpenFilename("Excel-files,*.xls", _
    1, "Select One File To Open", , False)

'User didn't select a file
If TypeName(FileName) = "Boolean" Then Exit Sub

'Open the workbook
Workbooks.Open FileName

End Sub


Sub OpenMultipleFiles()

Dim FileName As Variant, f As Integer

'Displaying the open file dialog box
FileName = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Select One Or More Files To Open", , True)

'User didn't select a file
If TypeName(FileName) = "Boolean" Then Exit Sub

'Open all the workbooks selected by user
For f = 1 To UBound(FileName)
    Workbooks.Open FileName(f)
Next f

End Sub

Sub SaveFile()

Dim FileName As Variant

'Displaying the saveas dialog box
FileName = Application.GetSaveAsFilename("MyFileName.xls", _
    "Excel files,*.xls", 1, "Select your folder and filename")

'User didn't save a file
If TypeName(FileName) = "Boolean" Then Exit Sub

'Save the workbook
ActiveWorkbook.SaveAs FileName

End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com



Example:


One thought on “Using Dialog box for opening and saving the workbook

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>