How to Open a Workbook using VBA in Excel

To open a workbook using VBA, you need to use the “Workbook.Open” method and specify the path of the file (make sure to specify the full path to the workbook with name and extension file type). This method has a total of fifteen optional arguments which you can use to deal with different kind of files.

In this tutorial, we will explore it in detail and look at an alternative method that you can use.

Steps to Open a Workbook using VBA

  1. To start the code, use 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 vba_open_workbook()
Workbooks.Open "C:\Users\Dell\Desktop\myFile.xlsx"
End Sub

Helpful Links: Run a MacroMacro RecorderVisual Basic EditorPersonal Macro Workbook

Workbook.Open Syntax

Now it’s time to look at the syntax of the method that you just have used in the above example. As I mentioned, there are fifteen arguments that you can use:

expression.Open (FileName, UpdateLinks, _
ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, _
Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

You won’t be using all these arguments. But a few of them are quite important and could be useful for you in the real world.

Opening a Password Protected Workbook

If you want to open a workbook that is password-protected, in that case, you can specify the password with the password argument.

Here I have a workbook on the desktop that has a password “test123” and now I want to open it and unprotect it at the same time. Following is the code that I need to use.

Workbooks.Open "C:\Users\Dell\Desktop\myFile.xlsx", , , Password:="test123"

Opening a Workbook as Read Only

When you open a workbook as read-only you can’t make changes to the same workbook, but you need to save a copy of it.

Workbooks.Open "C:\Users\Dell\Desktop\Folder\1.xlsx", , True

Open All the Workbooks from a Folder

Sub vba_open_multiple_workbooks_folder()
    Dim wb As Workbook
    Dim strFolder As String
    Dim strFile As String
        strFolder = "C:\Users\Dell\Desktop\Folder\"
        strFile = Dir(strFolder & "*.xls*")
        Do While strFile <> ""
            Set wb = Workbooks.Open(strFolder & strFile)
            strFile = Dir
        Loop
End Sub

To use it as per your needs, make sure to change the folder path.

Workbook Open File Dialog

Sub vba_open_dialog()
    Dim strFile As String
    strFile = Application.GetOpenFilename()
    Workbooks.Open (strFile)
End Sub