To open a workbook whose path is stored in a cell, read that cell into a VBA string and pass the path to Workbooks.Open. For example, put a full file path in Config!B2, then run a macro that checks the value and opens the workbook; the sample below includes basic blank-value, missing-file, and error handling.
What the cell value needs to contain
The cell should contain the path and file name of the workbook to open, not just a worksheet name or a partial file name. A local Windows example is C:\Reports\Quarterly.xlsx. A shared folder might use a UNC path such as \\ServerName\Finance\Quarterly.xlsx. The file extension should match the actual file, such as .xlsx, .xlsm, or .xls.
For a reliable first setup, enter the path as plain text in a dedicated cell. You can also have a formula produce the path, but the formula’s result still needs to be a valid path. A path assembled from a folder in one cell and a file name in another must include the appropriate backslash between them. Extra spaces at the beginning or end can cause a path check to fail, so the example trims those spaces.
This method opens a workbook. It does not open a document in another application, navigate to a worksheet, or automatically choose a file from a folder. Those are separate steps that can be added once the basic path-based opening works.
VBA code to open the workbook
In this example, the path is in cell B2 on a worksheet named Config in the workbook containing the macro. The code checks that the cell is not blank and that a file exists at the supplied path, then opens the target workbook as read-only. Read-only is a cautious default; remove that argument if the macro should open the file for editing.
Sub OpenWorkbookFromCell()
Dim sourceSheet As Worksheet
Dim filePath As String
Dim targetWorkbook As Workbook
' Read the path from the configuration cell.
Set sourceSheet = ThisWorkbook.Worksheets("Config")
filePath = Trim$(CStr(sourceSheet.Range("B2").Value))
If Len(filePath) = 0 Then
MsgBox "Enter a workbook path in Config!B2.", vbExclamation
Exit Sub
End If
If Len(Dir$(filePath, vbNormal)) = 0 Then
MsgBox "No file was found at: " & filePath, vbExclamation
Exit Sub
End If
On Error GoTo OpenError
Set targetWorkbook = Workbooks.Open(Filename:=filePath, ReadOnly:=True)
Exit Sub
OpenError:
MsgBox "Excel could not open the file: " & Err.Description, vbCritical
End Sub
The macro assumes that the source workbook really has a sheet named Config. If your sheet has another name, change that text in the code. The cell reference can also be changed from B2 to another cell. ThisWorkbook means the workbook that contains this VBA code; it is safer than ActiveWorkbook when opening another workbook could change which file is active.
How to add and run the macro
- Enter the full path of the target workbook in Config!B2, and confirm the spelling, extension, and folder location.
- Save a copy of the workbook that contains the macro before running it. VBA actions cannot be undone with Ctrl+Z.
- In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11 or Fn+Option+F11, depending on the keyboard, or open the editor from the Developer tab.
- In the editor, choose Insert > Module, paste the procedure into the module, and save the macro-containing workbook as an Excel Macro-Enabled Workbook (.xlsm).
- Return to Excel and run OpenWorkbookFromCell from Developer > Macros, or press Alt+F8 on Windows and select the macro. On Mac, use the Macros command on the Developer tab if the Windows shortcut is unavailable.
Excel may show a security notice when a workbook contains macros. Only enable macros in files you trust and that you expected to receive. Excel for the web can open workbooks, but it cannot run VBA macros; use the desktop Excel app to run this code.
Using another cell, a formula, or a named range
To use a different cell, replace sourceSheet.Range("B2") with the desired address, such as sourceSheet.Range("D5"). To read from a worksheet that may not be active, keep the worksheet reference explicit. Avoid relying on Range("B2") alone: an unqualified range refers to the active sheet, which can change while a macro is running.
A formula can create a path too. For instance, a cell might combine a fixed folder with a file name entered elsewhere. VBA reads the calculated value of the cell, rather than the formula text, when using .Value. Make sure the displayed result is a complete path and that the target file exists. If the formula returns an error such as #VALUE!, converting it to a string will not make it a usable path; test or correct the formula first.
For a workbook that other users will maintain, a named range can make the setup easier to understand than a hard-coded address. Define a name such as FileToOpen for the path cell, then refer to sourceSheet.Range("FileToOpen") in the code. The name must be defined in the workbook scope or on the relevant worksheet, and the macro’s worksheet reference must still point to the sheet containing it.
Opening read-only, editable, or a specific sheet
The sample opens the target read-only to reduce the chance of accidental changes. If users need to edit the target, change the opening line to Set targetWorkbook = Workbooks.Open(Filename:=filePath). Excel may still display prompts or open the file in a protected state depending on its source, security settings, and file format. Removing ReadOnly does not override permissions or make a locked file editable.
Opening a workbook does not select a particular sheet. If you also need to activate a sheet after opening, use the returned workbook object, for example targetWorkbook.Worksheets("Summary").Activate, and ensure that the target workbook actually contains that sheet. It is generally better to work through the workbook object than to assume the newly opened file will always be the active workbook.
If the target is a CSV or another supported workbook format, Workbooks.Open can open it, but Excel may interpret its contents differently from a normal .xlsx file. If you want to open a PDF or another non-workbook document, Workbooks.Open is not the right tool for the same purpose; use a method intended to launch that file type instead.
Handling missing files and shared locations
The Dir check gives a clear message when a local or accessible network file cannot be found. It is a preliminary check, not a guarantee that the file will open: permissions, a damaged workbook, an unsupported file type, or an Excel prompt can still prevent a successful open. The error handler catches an opening error and displays Excel’s description.
For shared workbooks, use a stable network location and check that the user running the macro has access. A mapped drive such as Z: may not be available to another person or in a different sign-in session, even when it works on your computer. A UNC path is often more portable within a managed Windows network, provided the user has permission. Cloud-synced folders can also differ by user because their local folder paths may not match.
The Dir-based existence check is designed for ordinary filesystem paths. Web URLs and some cloud locations do not behave like local paths, so this exact validation approach may not apply. If users select a file through a browser or a cloud service, use an approach designed for that location rather than assuming a URL is a Windows file path.
Common errors and how to fix them
- Subscript out of range: The sheet name in the code does not match a worksheet in the macro workbook, or the code is looking in a different workbook than intended. Check Config’s spelling and keep the
ThisWorkbookreference if the cell belongs to the macro file. - File not found: Check the complete path, extension, spaces, and folder. Confirm that the file has not been moved and that the current user can access the location.
- Permission or sharing error: The file may be open exclusively, read-only by policy, or inaccessible to the current account. Ask the file owner or administrator to verify permissions instead of trying to bypass them.
- The macro runs but opens the wrong file: Check what value the cell actually contains, including any formula result. Also confirm the code uses the intended worksheet and cell rather than an unqualified active-sheet reference.
- Macros are blocked: The code cannot run in Excel for the web, and desktop Excel may block macros based on its security settings or file source. Use a trusted macro-enabled workbook and follow your organization’s security policy.
When diagnosing a problem, inspect the cell value before changing the code. A quick check is to select the cell and verify the full path in the formula bar. If the path is built by a formula, check the evaluated result and ensure the folder separator is present exactly once where needed.
Windows and Mac considerations
The example targets Microsoft 365 for Windows, where paths usually use drive letters and backslashes. Excel for Mac supports VBA and the Workbooks.Open method, but file paths can use a different format, and access to files may depend on macOS privacy permissions. A Mac user should use a path that Excel on that Mac can access; do not assume a Windows path copied from another computer will work unchanged.
VBA is not available in Excel for the web. If a process must run in a browser, consider an Office Scripts or Power Automate solution where those tools meet the need and are available in the user’s Microsoft 365 plan. They are not VBA, and they have different capabilities and setup requirements.
Safety and workbook design
A macro that opens a path from a cell follows the value it is given. If another person can edit that cell, they may redirect the macro to a different file. Keep the path cell in a controlled location, validate the expected folder or file type where appropriate, and avoid running code that opens untrusted files automatically. A file existing at a path does not mean it is safe to open.
For routine use, a button assigned to the macro can be clearer than running it from the macro list. You can add a Form Control button from Developer > Insert and assign OpenWorkbookFromCell. Add a short label such as “Open selected workbook,” and tell users which cell supplies the path. A button does not remove the need for input checks or macro security.
Frequently Asked Questions
Can Excel open a workbook automatically when a cell changes?
Yes. A Worksheet_Change event procedure can watch a particular cell and call the opening logic when that cell is edited. This can be surprising if a user pastes or clears values, so a button or manually run macro is often safer. Event code belongs in that worksheet’s code module, not a standard module.
Can I use only a file name instead of the full path?
Sometimes, if the file is in Excel’s current folder or another location Excel can resolve, but relying on the current folder is fragile. A full path makes the intended location explicit and is easier to troubleshoot.
Will this work if the file is already open?
Excel may activate an already open workbook rather than create another independent copy, depending on how the same file is referenced. If duplicate-open handling matters, check the open workbooks before calling Workbooks.Open and compare their full names.
Can the cell contain a clickable hyperlink instead?
A hyperlink is not automatically the same thing as a plain path string. You can read a hyperlink’s address in VBA, but the sample expects the cell’s value itself to be a usable file path. For the simplest setup, store the path as text in the cell.
Can I use the same macro in Excel for the web?
No. Excel for the web does not execute VBA. Run the macro in desktop Excel, or investigate an alternative such as Office Scripts for a browser-based workflow.
Related
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA For Loop Syntax: Examples and Common VariationsIn VBA, a For...Next loop repeats code for a counter that moves from a starting value to an…
- VBA MsgBox Yes No Cancel: Buttons and ExampleTo show Yes, No, and Cancel in a VBA message box, call MsgBox with the vbYesNoCancel button option,…
- Excel VBA Option Explicit: What It Does and How to Use ItOption Explicit is a VBA module setting that requires you to declare variables before using them. Put it…
- Excel VBA in VS Code: What Works and What You NeedYes, you can use Visual Studio Code (VS Code) to edit Excel VBA code, but VS Code cannot…
- Excel VBA: Find the Last Used Row in Any WorksheetTo find the last row containing content anywhere on a worksheet in Excel VBA, use Range.Find with SearchOrder:=xlByRows…
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.