How to use VBA to open Workbook in Excel?
There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a sample code where I have given a browse button (shape) in an Excel sheet. User can click on this button/shape and select an Excel file. The browsed file path is then displayed in cell E2:
Steps to Open a VBA Workbook in Excel
- Start the code by using the “Workbooks” object
- Type a dot (.) after that and select the Open method from the list
- Specify the file path in the first argument and make sure to enclose it in double quotation marks
- In the end, run the code to open the workbook
Sub open_file()
Workbooks.Open "C:\Users\Dell\Desktop\myFile.xlsx"
End Sub
VBA Open File DialogBox – Excel Macros Example Code
VBA Code to browse file using File Dialog
Public Sub BrowseAFile()
Dim objFileDialog As Object
Dim objSelectedFile As Variant
'Browse the file
Set objFileDialog = Application.FileDialog(3)
With objFileDialog
.ButtonName = "Select"
.AllowMultiSelect = False
.Filters.Clear 'It is important to clear old filters before adding new one
.Filters.Add "Excel File", "*.xls;*.xlsx;*.xlsm", 1 'You may add more filters and give them a sequence
.Title = "Select Input file"
.Show
For Each objSelectedFile In .SelectedItems
Range("E2").Value = objSelectedFile 'You may change the destination as per your requirement
Next
End With
End SubBelow is the description of the available options in the code to customize the code for your requirement
- ButtonName: Name of the select button, I have used ‘Select’ in the code
- AllowMultiSelect: To allow user to select one or multiple files, I have allowed only one input file in the code
- Filters: To show only specific type of files to user for selection (Example: .Filters.Add “Word File”, “*.doc;*.docx”, 1 to select a word file)
It is worth to mention that if you want to allow user to select multiple files then you need to make few changes in the code else it will overwrite the path mentioned in cell E2 instead of creating a list
Steps to apply VBA to Open Workbook Excel File using File Dialog Box
To use this code in your Excel file, follow below steps:
- Open the Excel file where you want to count the color cells
- Press Alt+F11
- Insert a Module (Insert>Module) from menu bar
- Paste the code in the module
- Now add a shape in Excel sheet
- Give a name to the shape like ‘Browse a file’
- Right click on the shape and select ‘Assign Macro…’
- Select BrowseAFile from the list and click on ‘Ok’ button
Thats how you can browse excel file using VBA Code.
Download Practice File for Free
To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.
Recommended Articles
Excel VBA Course : Beginners to Advanced
We are currently offering our Excel VBA Course at discounted prices. This course includes On-Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
- Quickly Move or Delete Files or Folder >> Click here
- Handle Duplicate Files with Automated Utlity >> Click here
- Generate Dummy Data For Testing >> Click here
- Excel Files and Sheets Consolidator >> Click here
- Manage Outlook Emails efficiently >> CLick here
- Send Bulk Emails wiht our Excel Automation >> Click here
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
One Comment