Open Workbook

When using VBA to automate data processing, there are situations where you need to read data from an existing workbook.

VBA provides a way to open other workbooks, allowing you to open, retrieve, and manipulate data from a workbook using VBA.

This enables automatic data exchange between multiple workbooks. Additionally, you can process multiple workbooks containing serial numbers in bulk.

This page introduces how to open a workbook using VBA with specific code examples.

How to Open a Workbook Using VBA

To open a new workbook, use the Open method provided by the Workbooks class.

Workbooks.Open "File_Path"

This method opens the workbook at the file path specified as an argument.

Specify the file path as an absolute path like C:\Users\user\Documents\sample.xlsx.

Opening a Workbook with a Relative Path

If you want to specify a relative path, use the ThisWorkbook.Path property to get the path of the current workbook and specify the relative path to it.

Workbooks.Open ThisWorkbook.Path & "\sample.xlsx"

Executing the above code will open sample.xlsx located in the same directory as the executing Excel file.

Opening a Password-Protected Workbook

To open a password-protected workbook, specify the password in the Password argument.

Workbooks.Open("File_Path", Password:="Password")

Executing the above code will open the workbook protected by the specified password.

If the password is incorrect, an error will be output.

Opening a Workbook as Read-Only

If you only need to reference an external Excel file without making changes, you can open the workbook as read-only.

To open a workbook as read-only, specify True for the ReadOnly argument.

Workbooks.Open("File_Path", ReadOnly:=True)

Executing the above code will open the workbook as read-only.

To prevent accidental edits, it is recommended to open the workbook as read-only if you only need to reference it.

For more details on opening a workbook as read-only, see the following page.

Precautions When Opening a Workbook

When you open a workbook using the code mentioned above, Excel will actually launch in a new window.

Therefore, if an error occurs in the VBA code and the process is interrupted, Excel may remain open.

To prevent such situations, it is recommended to add a process to always close the workbook even if an error occurs.

For more details on handling errors, see the following page.

Specific Examples of Closing a Workbook

Basic Usage

The following code opens sample.xlsx and closes it after processing.

Private Sub OpenWorkbook()
    Dim wb As Workbook
    Dim path As String

    ' Specify the path of the workbook to open
    path = "C:\Documents\Report.xlsx"

    ' Open the workbook
    Set wb = Workbooks.Open(path)

    ' Perform some processing
    ' ...

    ' Close the workbook after processing (do not save changes)
    wb.Close SaveChanges:=False
End Sub

Processing Multiple Workbooks in Bulk

The following code processes multiple workbooks specified in an array one by one and closes them after processing.

Sub OpenMultipleWorkbooks()
    Dim wb As Workbook
    Dim files As Variant
    Dim i As Integer

    ' List of workbooks to open
    files = Array("C:\Reports\Report1.xlsx", "C:\Reports\Report2.xlsx", "C:\Reports\Report3.xlsx")

    ' Open each file in the array
    For i = LBound(files) To UBound(files)
        Set wb = Workbooks.Open(files(i))
        ' Perform necessary processing
        ' ...
        ' Close the workbook after processing (do not save changes)
        wb.Close SaveChanges:=False
    Next i
End Sub

Summary

Using VBA to open workbooks broadens the scope of VBA utilization and is very useful for automating tasks. Customize the code to suit your tasks and try using it.