VBA Open File DialogBox – Excel Macros Example Code

Home/VBA/VBA Open File DialogBox – Excel Macros Example Code

VBA Open File Dialog Box helps to browse a file and open Excel Workbook. Users can choose file by clicking on the button to open an Excel File with specific filter, title.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Code to Open an Excel File using File Dialog Box

We can open the Excel files using File dialog box using Application.GetOpenFilename method in VBA. The following are VBA Syntax and Examples to Open Excel Files using File Dialog Box.

VBA Code to Open an Excel File using File Dialog Box: Syntax

Here is the VBA code syntax to show the open dialog Box in Excel. Here we can specify the file filter, File dialog title to show.


strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

VBA Code to Open an Excel File using File Dialog Box: Example

Here is the VBA Code to Open an Excel File using File Dialog Box. Here we first capture the file path using File Dialog Box and then we can open the File using Workbook. Open method.

Sub sbVBA_To_Open_Workbook_FileDialog()
Dim strFileToOpen As String
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=strFileToOpen
End If
End Sub

Explained VBA Code to Open an Excel File using File Dialog Box

Here is the explaination to the above code.

‘Strating sub procedure to write VBA Code to Open an Excel File using File Dialog Box
Sub sbVBA_To_Open_Workbook_FileDialog_C()

‘Declaring a Variable to hold the file choosen using File DilogBox
Dim strFileToOpen As String

‘Choosing an Excel File using File dialog Box and capturing the file path in the variable
strFileToOpen = Application.GetOpenFilename _
(Title:=”Please select an Excel file to open”, _
FileFilter:=”Excel Files *.xls* (*.xls*),”)

‘Here you can note that we have allowed any excel files to choose
‘We have also customized the file dialog title

‘Checking if file is selected
If strFileToOpen = False Then

‘Displaying a message if file not choosedn in the above step
MsgBox “No file selected.”, vbExclamation, “Sorry!”

‘And existing from the procedure
Exit Sub
Else

‘Openning the file if selected in the above step
Workbooks.Open Filename:=strFileToOpen
End If

‘Ending the sub procedure
End Sub

VBA Code to Open Only (.xls) Excel 2003 format Files using File Dialog Box

You can filter the files to choose and open specific files with required file extensions. The below VBA code example will show you how to filter the excel files and allow the user to choose only .xls files.

'Strating sub procedure to write VBA Code to Open an only Excel 2003 Files using File Dialog Box
Sub sbVBA_To_Open_Workbook_FileDialog_xls_C()
'Declaring a Variable to hold the file choosen using File DilogBox
Dim strFileToOpen As String
'Choosing an Excel File using File dialog Box and capturing the file path in the variable
strFileToOpen = Application.GetOpenFilename _
(Title:="Please select an Excel file to open", _
FileFilter:="Excel Files *.xls (*.xls),")
'Here you can note that we have allowed any (.xls) excel files to choose
'We have also customized the file dialog title
'Checking if file is selected
If strFileToOpen = False Then
'Displaying a message if file not choosedn in the above step
MsgBox "No file selected.", vbExclamation, "Sorry!"
'And existing from the procedure
Exit Sub
Else
'Openning the file if selected in the above step
Workbooks.Open Filename:=strFileToOpen
End If
'Ending the sub procedure
End Sub

VBA Code to Open Only (.xlsm) Excel 2007+ format Macro Files using File Dialog Box

You can restric the users to choose specific files with required file extensions. The below VBA code example will show you how to filter the excel files and allow the user to choose only 2007 -2013 excel macro files (.xlsm) files.

'Strating sub procedure to write VBA Code to Open an only Excel 2007 macro Files using File Dialog Box
Sub sbVBA_To_Open_Workbook_FileDialog_xlsm_C()
'Declaring a Variable to hold the file choosen using File DilogBox
Dim strFileToOpen As String
'Choosing an Excel File using File dialog Box and capturing the file path in the variable
strFileToOpen = Application.GetOpenFilename _
(Title:="Please select an Excel file to open", _
FileFilter:="Excel Files *.xlsm (*.xlsm),")
'Here you can note that we have allowed any (.xlsm) excel macro files to choose
'We have also customized the file dialog title
'Checking if file is selected
If strFileToOpen = False Then
'Displaying a message if file not choosedn in the above step
MsgBox "No file selected.", vbExclamation, "Sorry!"
'And existing from the procedure
Exit Sub
Else
'Openning the file if selected in the above step
Workbooks.Open Filename:=strFileToOpen
End If
'Ending the sub procedure
End Sub
LIMITED TIME OFFER
By |April 22nd, 2014|VBA|8 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

8 Comments

  1. Fredy Armenta August 29, 2014 at 3:20 AM - Reply

    Is not supposed to be “False” (with quotes) instead of False (without quotes)?
    By the way, thank you very much for the code and the explanation.

  2. Mahantesh December 5, 2014 at 10:28 AM - Reply

    Hi,

    It shud be “If strFileToOpen = “” “and not “If strFileToOpen = False”.

    Regards,
    Mahantesh

  3. Mike March 9, 2015 at 9:29 PM - Reply

    This worked for me:

    If strFileToOpen = “False” Then

    No file was selected, so strFileToOpen returns a value of False.
    However you’ve initialized the variable as a string, so you must put the return value in quotes or you get a type mismatch error.

  4. Saadia March 25, 2015 at 9:19 PM - Reply

    it doesn’t work for me it gives me this error :
    execution error 428, object not found

    • August 6, 2015 at 10:06 PM - Reply

      Hi Saadia, pls. replace the following code

      If strFileToOpen = False Then

      With
      If strFileToOpen = “” Then

  5. Jin April 7, 2016 at 4:42 PM - Reply

    If strFileToOpen = Null

  6. Brian January 4, 2017 at 7:37 PM - Reply

    I too have had an issue with the sample code working properly. I think the issue is definition of the variable strFileToOpen. The sample code shows it to be of type String. It should be of type Variant. That way “False” works when no file is selected (i.e. selecting Cancel in the dialog box) and yet will open the file properly when a file is selected.

  7. Tushar Rawat February 22, 2017 at 11:38 AM - Reply

    Hello All

    I want to know how to get the file name. As I wanted to expand a macro little bit so need your help guys. I want to get the file name ( which will be changing as it is dependent on the user which file he selects) so that I can copy the data from it and paste it into my dashboard file.

    Thanks In Advance.
    Regards
    Tushar Rawat

Leave A Comment


Related pages


highlighting duplicates in excelshortcut keys for excelhow to sort from highest to lowest in excelvba excel projectsinsert new worksheet in excelvba inputbox cancelexcel pie chart templateexcel macros for dummiesformula vlookup in excelexcel formula chartexcel vba activate workbookhow do you sort columns in excelvba cell interior colorexcel vba beginnerhow to protect worksheet in excel 2007excel 2007 pivot table tutorialremove blank excel rowshow to insert macro in excel 2007rows.count vbaexcel vba paste rangesort excel column alphabeticallyworkbooks.open filenameunlocking password protected excelvb color codes listvba selection.sortexcel remove hyperlinkformatting charts in excelvba headerhyperlink excel vbacombobox controlexcel vba tips and tricksexcel msgboxsample project plan template excelexcel command buttonsvba delete recorddelete duplicates on excelexcel dynamic cell referencedouble vbaexcel vba save worksheetcombination chart in excel 2007explain ddl and dmlcapacity planning excel template freeexcel macro password removervb listboxvba move filesexcel calculate sheet shortcutexcel vba display userformvba excel accessduplicates in excel columncreate pie chart in excel 2007sql coding for beginnersuserform closesumifs formula in excelrun sql query in excelfrequently asked aptitude questions with answersexcel worksheet name from cell valuehow to enter a macro in excelpractice pivot tablesvba convert to integerexcel string comparison operatorsworkbook and worksheet in ms excelexcel vba protect workbookinputbox excel vbainserting blank rows in excelexcel 2007 listboxpivots excelexcel vba templatesvlookup templatevba meaningexcel offset vbamacro formula in excelexcel vba protect workbookmerge rows excelunlock a protected excel sheetmultiple if functions in excel 2010excel dynamic advanced filtermerge and center on excel