Thread: Word VB Routine to vlookup in an Excel Spreadsheet

Hello

I am trying to write a routine for Word that looks up report data from an excel spreadsheet.
The spreadsheet has worksheet called Report with project numbers in column E and the financials for each project in column R (an offset of 13 columns).

I have set up excel in the references window for the VBA project and I can look up a specific cell correctly eg:
Selection.TypeText Text:=myWB.Sheets("Report").Range("R10")
returns the data in that cell

But how do I do the equivalent of a vlookup so I can find the row with the matching project number and then lookup the financials from column R in that row?

I have tried (1234 is the project number):

Sub ExcelLookup()

Dim myWB As Excel.Workbook, varTest
Set myWB = GetObject("D:\SampleSpreadsheet.xls")
varTest = myWB.Application.WorksheetFunction.VLookup(1234, "Report!E1:R100", 13)
Set myWB = Nothing
Debug.Print varTest

End Sub

I have spent all day looking at various solutions on the www but none seem to work.

Thanks in advance

Joel