An Excel Macro to Open a Specific User-Defined Workbook
Want to give yourself or your users a quick way to search for and open a file in Excel? This macro uses a simple technique that opens a friendly dialog box, allowing you to browse for and open the Excel file of your choosing.
How the macro works
This macro opens the dialog box you see here, allowing the user to browse for and open an Excel file.
Here’s how this macro works:
Sub Macro1() 'Step 1: Define a String variable Dim FName As Variant 'Step 2: GetOpenFilename Method activates dialog box FName = Application.GetOpenFilename( _ FileFilter:="Excel Workbooks,*.xl*", _ Title:="Choose a Workbook to Open", _ MultiSelect:=False) 'Step 3: If a file was chosen, open it! If FName <> False Then Workbooks.Open Filename:=FName End If End Sub
In Step 1, the macro declares a Variant variable that holds the filename that the user chooses. FName is the name of your variable.
In Step 2, you use the GetOpenFilename method to call up a dialog box that allows you to browse and select the file you need.
The GetOpenFilename method supports a few customizable parameters. The FileFilter parameter allows you to specify the type of file you’re looking for. The Title parameter allows you to change the title that appears at the top of the dialog box. The MultiSelect parameter allows you to limit the selection to one file.
If the user selects a file from the dialog box, the FName variable is filled with the chosen filename. In Step 3, you check for an empty FName variable. If the variable is not empty, you use the Open method of the Workbooks object to open the file.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.
Right-click project/workbook name in the project window.
Type or paste the code in the newly created module.
(Optional) Assign the macro to a button.