[Excel VBA] How to Open and Control an Existing Workbook by File Path

When building VBA macros, you often need to perform operations across different workbooks. For example, you might want to open another Excel file to read data or write results to a separate file.

This article explains the most basic and important method to open an existing Excel workbook using a specific path and control it safely using an object variable.

Complete VBA Code

Below is the VBA code that opens a workbook named “DataSource.xlsx” located in the same folder as the macro file. It edits a cell, saves the file, and closes it.

Sub OpenAndEditWorkbook()

    ' 変数を宣言します
    Dim targetBookPath As String
    Dim openedBook As Workbook

    ' 開きたいブックのフルパスを指定します
    targetBookPath = ThisWorkbook.Path & "\DataSource.xlsx"
    
    ' --- 1. ファイルの存在を確認 ---
    If Dir(targetBookPath) = "" Then
        MsgBox "指定されたファイルが見つかりません。" & vbCrLf & targetBookPath, vbExclamation
        Exit Sub
    End If

    ' --- 2. ブックを開き、そのオブジェクトを変数に格納 ---
    ' Workbooks.Openが返した「開かれたブック」をopenedBook変数にセットします
    Set openedBook = Workbooks.Open(targetBookPath)
    
    ' --- 3. 開いたブックをオブジェクト変数経由で操作 ---
    With openedBook.Worksheets("Sheet1")
        .Range("A1").Value = "この値はVBAによって書き込まれました。"
        .Range("A1").Font.Bold = True
    End With
    
    ' --- 4. 開いたブックを保存して閉じる ---
    ' SaveChanges:=True で変更を保存して閉じます
    openedBook.Close SaveChanges:=True
    
    ' --- 5. オブジェクト変数を解放 ---
    Set openedBook = Nothing

    MsgBox "外部ブックの操作が完了しました。"

End Sub

Key Points of the Code

1. Check if the File Exists (Dir Function)

If Dir(targetBookPath) = "" Then
    MsgBox "指定されたファイルが見つかりません。"
    Exit Sub
End If

Before executing Workbooks.Open, it is a very good habit to check if the target file actually exists using the Dir function. If the file does not exist, the Dir function returns an empty string (""). You can use this to prevent errors and show a clear message to the user.

2. Open the Workbook and Store it in a Variable (Workbooks.Open)

Set openedBook = Workbooks.Open(targetBookPath)

This is the most important part of manipulating external workbooks.

• Workbooks.Open("File Path") runs and opens the specified workbook.
• This method returns the opened workbook object itself.
• We use the Set keyword to assign that returned object to the openedBook variable.

From this point on, the openedBook variable acts as a specific reference to the newly opened workbook. This eliminates the risk of targeting the wrong workbook (which can happen when relying on ActiveWorkbook) and greatly improves code reliability.

3. Operate and Close the Workbook

With openedBook.Worksheets("Sheet1")
    ' ...
End With
openedBook.Close SaveChanges:=True

All operations and closing actions are done using the openedBook variable. This makes it clear in the code which workbook you are controlling. The argument SaveChanges:=True in the .Close method is a useful option to save the file before closing it.

Useful Arguments for Workbooks.Open

The Workbooks.Open method has other useful arguments:

• ReadOnly:=True: Opens the workbook as read-only. Use this if you want to prevent accidental data changes.
• UpdateLinks:=0: Prevents updating external links when opening the workbook. This stops update prompts and opens the book faster.
• Password:=”Secret”: Specifies the password inside the code if the workbook is protected.

Summary

The safest and recommended procedure for opening and controlling existing workbooks in VBA is:

1. Prepare the full path of the workbook you want to use.
2. (Recommended) Check if the file exists using the Dir function.
3. Open the workbook and store it in an object variable using Set wb = Workbooks.Open(filepath).
4. Perform all operations through that object variable.
5. Finally, close the workbook using .Close.

Using an object variable to “capture” the workbook is a fundamental and essential technique for building stable VBA macros.