An Excel Macro to Open a Specific User-Defined Workbook - dummies

An Excel Macro to Open a Specific User-Defined Workbook

By Michael Alexander

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.

The Open dialog box activated by the macro.
The Open dialog box activated by the macro.

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", _
'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:

  1. Activate Visual Basic Editor by pressing Alt+F11 on your keyboard.

  2. Right-click project/workbook name in the project window.

  3. Choose Insert→Module.

  4. Type or paste the code in the newly created module.

  5. (Optional) Assign the macro to a button.