Open a Specific Workbook Defined by the User

Learn how to Open a Specific Workbook Defined by the User

This is quite useful when you want to provide user with ability to select and open a workbook from within Excel session with a browsing window

When you want to give yourself or your users a quick way to search for and open a file?

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 it works (Open a Specific Workbook)

This code is triggered by a macro Shortcut key or a Button from within a workbook (see demo below)

When you press the shortcut keys of macro or press the button for the macro, this macro will open a file browsing window and lets you choose to open workbook of your choice

This is exactly same as if you were using from Excel > File > Open

Ready to use/apply VBA Code (double click to select entire code)

[Vb]

Sub OpenSpecificWorkbook()

‘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

[/Vb]

The Macro once executed opens a dialogue box > File Open Dialog box as you see on the right side

Then a user can select file he/she wants to open

How to use it?

To implement this macro, you need to copy and paste it into the module in an existing or brand new workbook

Placing the macro here allows it to run each time you try to Open the workbook.

Activate the Visual Basic Editor by pressing ALT+F11.
Right-click the project/workbook name in the Project window.
Choose Insert⇒Module.
Type or paste the code in the newly created module.
Optionally, you can assign the macro to a button

Detailed Explanation

You have to have your Excel workbook in a macro-enabled format which is XLSM or XLSB, it can be either an existing workbook or you can create a brand new one

After opening the workbook, press ALT + F11 or go to the Developer tab and click on Visual basic as you can see in GIF animation above

You need to add a new module inside this workbook

Paste the code into this newly created module

The first thing this macro does is to declare a variant variable that holds the filename that the user chooses. FName is the name of our variable.

In Step 2, we use the GetOpenFilename method to call up a dialogue box that allows us to browse and select the file we need.

The GetOpenFilename method supports a few customizable parameters.
The FileFilter parameter allows us to specify the type of file we are looking for.

The Title parameter allows us to change the title that appears at the top of the dialogue box.

The MultiSelect parameter allows us to limit the selection to one file.

If the user selects a file from the dialogue box, the FName variable is filled with the name of the file they have chosen.

In Step 3, we check for an empty FName variable. If the variable is not empty, we use the Open method of the Workbooks object to open the file.

Now you can save the workbook, and go back to the Excel window

You can test or run the macro either by allocating the same a Button in any worksheet/tab or assigning a shortcut key from view macro dialogue box

After you run this macro with any of the above methods, you will see a file open dialogue box with files only shows files that can be opened in Excel program and no other file (eg PDF or Word)

Also check out my blog post on how to Protect a Specific Worksheet while closing a Workbook here

If you want to master Excel in Professional way, check out my FREE EBook available for download, this Ebook contains more of these ready to apply Excel VBA codes and formulas! (Just fill below form to get this amazing Ebook for FREE)

If you have any question or query regarding above code usage, let me know in the comments below!