VBA & Macro Problems

Use a Workbook Path Stored in an Excel Cell for VLOOKUP

Khadija KhanKhadija Khan Nov 18, 2026 870 views

Question details

Use a workbook path stored in a cell to perform a VLOOKUP against another workbook with Excel VBA.

Use a Workbook Path Stored in an Excel Cell for VLOOKUP
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA routine reads a full file path from a worksheet, opens or identifies the source workbook, and looks up a value in its data.
Observed behavior
The lookup depends on a valid path, the correct workbook and range, error handling, and appropriate workbook cleanup. The reported information does not establish a specific error.
Before you start

Check that the path cell contains the full local file path, including the workbook extension, and confirm the source sheet name and lookup columns. Use a macro-enabled workbook and enable macros only for code you trust.

Solution 1Recommended

Open the Source Workbook and Handle the VLOOKUP Result in VBA

Use this macro pattern to retain a reference to the source workbook, handle lookup errors, and close it only if the macro opened it.

The example reads the path from Sheet1!B1 and the lookup value from Sheet1!B2 in the macro workbook. It searches columns A:B on Sheet1 of the source workbook and writes the result to Sheet1!B3.

This is a macro to run from Excel, not a worksheet function to call from a cell. Adjust the sheet names, cells, lookup range, and return-column index to match your workbooks.

1
Prepare the cells

In the macro workbook, enter the full source workbook path in Sheet1 cell B1 and the lookup value in B2. Confirm that the lookup key is in column A and the value to return is in column B of the source Sheet1.

2
Add the macro

Press Alt+F11, choose Insert > Module, and paste the following code: Sub LookupFromPath() Dim target As Worksheet, source As Workbook, candidate As Workbook Dim filePath As String, result As Variant, errorText As String Dim openedHere As Boolean On Error GoTo Failed Set target = ThisWorkbook.Worksheets("Sheet1") filePath = Trim$(CStr(target.Range("B1").Value)) If Len(filePath) = 0 Then Err.Raise vbObjectError + 1, , "Enter a workbook path in B1." For Each candidate In Application.Workbooks If StrComp(candidate.FullName, filePath, vbTextCompare) = 0 Then Set source = candidate Exit For End If Next candidate If source Is Nothing Then If Dir$(filePath) = "" Then Err.Raise vbObjectError + 2, , "The source file was not found." Set source = Workbooks.Open(Filename:=filePath, UpdateLinks:=0, ReadOnly:=True) openedHere = True End If result = Application.VLookup(target.Range("B2").Value, source.Worksheets("Sheet1").Range("A:B"), 2, False) If IsError(result) Then target.Range("B3").Value = "No match or lookup error" Else target.Range("B3").Value = result End If If openedHere Then source.Close SaveChanges:=False Exit Sub Failed: errorText = Err.Description On Error Resume Next If openedHere Then source.Close SaveChanges:=False On Error GoTo 0 MsgBox errorText, vbExclamation, "Lookup failed" End Sub

3
Run and check the lookup

Return to Excel, open Developer > Macros, select LookupFromPath, and click Run. Check B3 for the result; if the macro reports an error, verify the path, source sheet name, and range.

Open the Source Workbook and Handle the VLOOKUP Result in VBA
Workbook state: The macro reuses a workbook already open at the specified path and leaves it open. A workbook opened by the macro is opened read-only and closed without saving.
Free Microsoft Office alternative

Work with Lookup Tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue working with lookup tables, while checking VBA macro compatibility separately.

Open and edit Excel workbook formats such as XLSXWork with lookup tables and spreadsheet formulasUse a familiar spreadsheet interface for an easier transitionChoose a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What should the path cell contain?

Enter the full local path to the source workbook, including its filename and extension, such as C:\Data\Source.xlsx. Check that the file exists at that location.

Why does VLOOKUP return an error even when the workbook opens?

Check the source sheet and range, make sure the lookup value is in the range's first column, and verify the return-column index. With an exact match, VLOOKUP also returns an error when it cannot find the value.

Why use a Variant for the VLOOKUP result?

Application.VLookup can return an Excel error value instead of a normal result. Store it in a Variant and check IsError(result) before using or writing the result.

Should the macro close a workbook that was already open?

Usually not. Track whether the macro opened the workbook and close it only in that case, so an existing user session is left open.

Can WPS Spreadsheet run this Excel VBA macro unchanged?

Not necessarily. VBA availability and compatibility depend on the WPS version and environment. Test the macro in your setup; WPS does not directly fix an Excel VBA error.