VBA to Open Workbook in Excel

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:

VBA code to browse a file

Steps to Open a VBA Workbook in Excel

  1. Start the code by using the “Workbooks” object
  2. Type a dot (.) after that and select the Open method from the list
  3. Specify the file path in the first argument and make sure to enclose it in double quotation marks
  4. 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 Sub

Below is the description of the available options in the code to customize the code for your requirement

Excel VBA Master Tool Kit
FileDialog Options Excel
  • 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’
Shape in Excel
  • Right click on the shape and select ‘Assign Macro…’
Assign Macro in Excel
  • Select BrowseAFile from the list and click on ‘Ok’ button
Assign Macro Dialog in Excel

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.

Download Practice File

Recommended Articles

Similar Posts

employee-database-management
| |

Employee Database Tool

Byexcelsirji

Employee Database is an MS Access based tool to manage employee details. The tool supports upto 78 demographics for each employee such as Name, Location, Phone, Email, Address etc. The tool also comes with inbuilt attendance tracker to track daily attendance of employees. Over and above this, you can also design your own trackers and start using it.

VBA Code to Count Cells by Color
| |

VBA Code to Count Cells by Color

Byexcelsirji

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

VBA Code to Check if File Exist in Folder

VBA Code to Check if File Exist in Folder

Byexcelsirji

To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.

One Comment

  1. Pingback: ExcelSirJi | VBA TRICKS | VBA to Read Excel Data using Connection String

Leave a Reply

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

Excel VBA Master Tool Kit