We already know how to open or save an Excel file in VBA. We simply use the Open and SaveAs method of Workbook object. But that requires hard-coding of the path of the file. But most of the time you will want the end user to select a file using GUI or say File Open or Save As Dialog box that lets the user choose the location of the file visually and easily.
In this article, we cover the code used for displaying workbook open dialog box and save as dialog box.
I have attached a workbook that you can download. Workbook attached to this article contains three macros
In this code, we will use GetOpenFilename method of Application. The syntax of the GetOpenFilename method is:
Application.GetOpenFilename([FileFilter],[FilterIndex],[Title],[ButtonText],[MultiSelect])
[FileFilter]: You can define to show only one kind of file in the select folder. If you write "Excel-Files, *.xlsx, *.xls, *.xlsm" etc. then only excel files will be shown from the folder in the file open dialog box.
[FilterIndex]: It is the number of file filters you want to use.
[Title]: The Title of the dialog box.
[ButtonText]: For specifying the button text. Not important.
[MultiSelect]: It is a Boolean variable. If you set it True or 1, you will be able to select more than one file. If you set it false, you can only select only one file.
Enough of the theory. Let's do some spells.
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
How does it work?
When you run this segment of code, the GetOpenFilename method for the Application object will open an Open File Dialog box. The title of the dialog box will be "Select One File To Open" as we defined in the code. If you select a file then the Worbook.Open code will run and the file will be opened. If you don't select a file, the sub will exit without running the Workbook.Open code.
This segment of code will open the file open dialog box but you will be able to select more than one file at one time.
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
Note that here we have set the multiselect variable to True. This will enable the multiple selection of the file.
To open a Save As dialog box we will use the GetSaveAsFilename method of the Application object. Syntax of the method is:
Application.GetSaveAsFilename([InitialFileName],[FileFilter],[FilterIndex],[Title],[ButtonText])
[InitialFileName]: The initial file name. If you don't rename the file while saving it, your file will be saved with this name.
[FileFilter]: You can define to show only one kind of file in the select folder. If you write "Excel-Files, *.xlsx, *.xls, *.xlsm" etc. then only excel files will be shown from the folder in the file open dialog box.
[FilterIndex]: The filter index of the file.
[Title]: The title of the dialog box.
[ButtonText]: This is used in Mac system to change the name of the button.
Please follow below for the code
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
SaveFile Macro uses GetSaveAsFilename method of Application object to open save as dialog box, assigning the file name, and selecting the location for saving the workbook.
So yeah guys, this is how you can use dialog box to open and save files using VBA. I hope it was helpful. If you have any doubts regarding this article or any other VBA topic, ask in the comments section below.
Related Articles:
Use a closed workbook as a database (DAO) using VBA in Microsoft Excel | To use a closed workbook as a database with DAO connection use this VBA snippet in Excel.
Use a closed workbook as a database (ADO) using VBA in Microsoft Excel | To use a closed workbook as a database with ADO connection use this VBA snippet in Excel.
Getting Started With Excel VBA UserForms | To insert data to database, we use forms. The Excel UserForms are useful for getting information from the user. Here is how you should start with VBA userforms.
Change the value/content of several UserForm-controls using VBA in Excel | To change the content of the userform controls use this simple VBA snippet.
Prevent a userform from closing when the user clicks the x-button by using VBA in Excel | To prevent the userform from closing when the user clicks on the x button of the form we use UserForm_QueryClose event.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Thanks for sharing. Always useful