VBA & Macro Problems

Use Word VBA to Find an Excel Client ID and Retrieve the Adjacent Value

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 870 views

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.

Use Word VBA to Find an Excel Client ID and Retrieve the Adjacent Value
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the workbook and Word document

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.

2
Add the macro in Word

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

3
Set the lookup location

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.

4
Run the lookup

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.

Preserve identifiers and sensitive values: Store IDs with leading zeros as text in Excel and select the same text in Word. The example inserts the underlying cell value, not its formatted display, and does not copy it to the clipboard.
Free Microsoft Office alternative

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.

Open and edit Microsoft Word .docx documents and Excel .xlsx workbooks.Use spreadsheet Find to locate a client ID and inspect its adjacent value manually.Work in a familiar document and spreadsheet interface with minimal retraining.Use a free, lightweight suite for routine Office editing; VBA availability varies by edition and platform.
QA img-9

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.