Background
Unlike Excel, Word does not have the concept of cell addresses like “A1” or “F6”. Therefore, I investigated how to extract data from a specific position in a Word table and search for that data within an Excel file.
I achieved this by specifying the Word table position using (row, column) coordinates and comparing the retrieved value against Excel data using the InStr() function.
Goal
- Get the value located in a Word table at coordinates (2, 2).
- Check if this value exists anywhere in Row 1, Columns A through Z of an Excel file.
- Display the address of the found cell in a message box.
VBA Code (Word Side)
Below is the code I used. Run this as a macro within Word.
Sub SearchExcelFromWord()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim folderPath As String
Dim fileName As String
Dim searchValue As String
Dim cell As Object
Dim found As Boolean
Dim doc As Document
Dim tbl As Table
Dim col As Integer
Set doc = ActiveDocument
Set tbl = doc.Tables(1)
' Get text from cell (2,2) and remove newlines/trailing characters
searchValue = tbl.Cell(2, 2).Range.Text
searchValue = Replace(searchValue, vbCr, "")
searchValue = Left(searchValue, Len(searchValue) - 1)
Set xlApp = CreateObject("Excel.Application")
' Update the path to your specific folder
folderPath = "C:\Users\mori\Desktop\"
fileName = Dir(folderPath & "*.xlsx")
If fileName = "" Then
MsgBox "No Excel file found in the folder."
Exit Sub
End If
Set xlBook = xlApp.Workbooks.Open(folderPath & fileName)
Set xlSheet = xlBook.Sheets(1)
found = False
For col = 1 To 26
' Check if the cell value contains the search value
If InStr(1, xlSheet.Cells(1, col).Value, searchValue) > 0 Then
MsgBox "Found at " & xlSheet.Cells(1, col).Address(0, 0)
found = True
Exit For
End If
Next col
If Not found Then
MsgBox "Value not found"
End If
xlBook.Close SaveChanges:=False
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set tbl = Nothing
Set doc = Nothing
End Sub
Key Points of the Code
tbl.Cell(2,2).Range.Text: This retrieves the value from the specified cell in the Word table.InStr()Function: We use this to perform a “partial match” search against the cell values in Excel.- Data Cleaning: You must remove the newline characters and special control characters (like the end-of-cell marker) attached to the end of the Word data. If you skip this step, the comparison will fail.
Applicable Scenarios
- Checking if data in a Word document exists in an Excel master list.
- Extracting related data using an ID or code entered in Word.
- Using Word as a front-end interface and Excel as a backend database.
Summary
To get values from a Word table and search for them in Excel, the combination of Coordinate specification (Cell(row, col)), String manipulation, and the InStr() function is very effective.
By combining VBA in this way, you can seamlessly link different Office applications. This is a practical technique for daily tasks, so please give it a try.
