Use Word VBA to Find an Excel Client ID and Retrieve the Adjacent Value
Question details
Use selected text in a Word document to find a client ID in an Excel workbook and retrieve the value from the adjacent cell.

- Product
- Microsoft Word VBA and Microsoft Excel
- Device & OS
- not provided
- Scenario
- Client IDs are stored in Excel column A, and corresponding passwords or other values are stored in column B.
- Observed behavior
- The desired macro searches for the selected client ID, retrieves the matching value from column B, and closes the Excel workbook without saving changes.
For the automation example, use desktop Word and Excel on Windows, and confirm the workbook path, worksheet name, and lookup columns. If column B contains passwords, use only an authorized workbook and consider whether placing those passwords in a Word document is appropriate.
Retrieve the adjacent Excel value with a Word VBA macro
Use this approach to look up a selected client ID and insert the matching column B value immediately after it in Word.
This example uses late-bound Excel automation, so you do not need to add an Excel object-library reference. It searches column A for an exact, case-insensitive match and retrieves the first matching row's column B value.
In Excel, verify that client IDs are in column A and the values to retrieve are in column B. Note the workbook's full path and worksheet name. In Word, save the document as a Word Macro-Enabled Document (*.docm) if you want to retain the macro in that document.
In desktop Word on Windows, press Alt+F11, choose Insert > Module, and paste this code: Sub LookupAdjacentExcelValue() Dim xlApp As Object, wb As Object, ws As Object Dim matchCell As Object Dim selectedRange As Range, outputRange As Range Dim clientID As String, adjacentValue As String Dim failureMessage As String Set selectedRange = Selection.Range.Duplicate clientID = Trim$(Replace(Replace(selectedRange.Text, vbCr, ""), Chr(7), "")) If Len(clientID) = 0 Then MsgBox "Select a client ID in Word first." Exit Sub End If On Error GoTo Failed Set xlApp = CreateObject("Excel.Application") Set wb = xlApp.Workbooks.Open("C:\Data\Clients.xlsx", 0, True) Set ws = wb.Worksheets("Sheet1") Set matchCell = ws.Columns("A").Find(clientID, ws.Cells(ws.Rows.Count, 1), -4163, 1, 1, 1, False, False, False) If matchCell Is Nothing Then MsgBox "No matching client ID was found." Else adjacentValue = CStr(matchCell.Offset(0, 1).Value2) Set outputRange = selectedRange.Duplicate outputRange.Collapse wdCollapseEnd outputRange.InsertAfter " " & adjacentValue End If GoTo CleanUp Failed: failureMessage = "Lookup failed: " & Err.Description CleanUp: On Error Resume Next If Not wb Is Nothing Then wb.Close False If Not xlApp Is Nothing Then xlApp.Quit Set matchCell = Nothing Set ws = Nothing Set wb = Nothing Set xlApp = Nothing On Error GoTo 0 If Len(failureMessage) > 0 Then MsgBox failureMessage End Sub
Replace C:\Data\Clients.xlsx and Sheet1 in the code with your workbook path and worksheet name. Change ws.Columns("A") if the IDs are elsewhere, and adjust matchCell.Offset(0, 1) if the return value is not one column to the right. The Find arguments -4163 and 1 specify cell values and a whole-cell match.
Return to Word and select only the client ID text. Press Alt+F8, select LookupAdjacentExcelValue, and click Run. The macro inserts the retrieved value after the selection, then closes the workbook without saving and quits the Excel instance it created.
Close the Excel workbook without saving in an existing macro
Use this targeted change if your lookup already works and you only need to prevent saving changes when the workbook closes.
Work with Word Documents and Excel Lookup Lists in WPS Office
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and Microsoft Office format compatibility that makes migration straightforward. Use WPS Writer and Spreadsheets to review the document and client list manually; WPS does not fix Microsoft's Word-to-Excel automation, and this VBA workflow should not be assumed to run unchanged.

Frequently Asked Questions
Why should I change .Close to .Close False?
On an Excel workbook object, .Close False explicitly closes without saving changes. In a With wb block, use .Close False; otherwise, use wb.Close False. It does not undo text already inserted into Word.
Why does the macro fail to find a client ID that is visible in Excel?
Check the workbook path, worksheet name, and search column in the VBA editor. This example uses a whole-cell match, so extra spaces or IDs stored as numbers instead of text can prevent a match. For IDs such as 00123, store the Excel ID as text and select the same characters in Word.
What happens if more than one Excel row has the same client ID?
The example returns the first match found when searching from the top of column A. If IDs are not unique, review the duplicates in Excel before running the macro or extend the code with FindNext and an explicit rule for choosing a record.
Can I use mail merge instead of a VBA macro?
Yes, if your goal is to populate a document from client records. In Word, choose Mailings > Select Recipients > Use an Existing List, select the Excel workbook and worksheet, and add the needed field through Insert Merge Field. Use Edit Recipient List to filter by client ID, then Preview Results to check the value. Mail merge does not automatically use the text currently selected in Word.
Will this Word VBA automation run in WPS Office?
Do not assume it will. The example depends on desktop Word VBA and Microsoft Excel automation on Windows. WPS macro support varies by edition and platform. For a manual alternative, open the workbook in WPS Spreadsheets, press Ctrl+F to locate the client ID, and copy the adjacent value into WPS Writer only if you are authorized to use it.




