Performing a vlookup from word and populating bookmarks

[Digby]

Hello,

I would like to perform a vlookup in a specific excel file from my word template.
The user would insert a number into a userform in word, which would return two pieces information from cells in the same row into bookmarks in word.

If anyone could give me pointers on how to run a function in excel and return the results into word, that would be extremely useful and give me something to start having a go at.

[HansV]

Welcome to Eileen's Lounge!

You can use Automation to control Excel from Word. For example:

Code: Select all

Private Sub cmdOK_Click()
    ' Modify as needed
    Const strFilename = "C:\Excel\Data.xlsx" ' Path and filename of Excel workbook
    Const strSheetName = "MySheet" ' Name of worksheet
    Const strRange = "A2:C100" ' Address of lookup range
    Const strBookmark1 = "ThisBookmark" ' Name of first bookmark
    Const strBookmark2 = "ThatBookmark" ' Name of second bookmark

    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim xlRng As Object
    Dim lngNum As Long

    If Me.txtNum = "" Then
        Me.txtNum.SetFocus
        MsgBox "Please enter a number!", vbExclamation
        Exit Sub
    End If
    lngNum = Val(Me.txtNum)

    On Error GoTo ErrHandler
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(strFilename)
    Set xlWsh = xlWbk.Worksheets(strSheetName)
    Set xlRng = xlWsh.Range(strRange)

    ActiveDocument.Bookmarks(strBookmark1).Range.Text = xlApp.Vlookup(lngNum, xlRng, 2, False)
    ActiveDocument.Bookmarks(strBookmark2).Range.Text = xlApp.Vlookup(lngNum, xlRng, 3, False)

ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=False
    xlApp.Quit
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Change the constants at the beginning of the code as needed.
The code assumes that your userform contains a text box txtNum and a command button cmdOK.

[Digby]

Thank you very much Hans, this is a superb piece of code, works like a dream!