Use VBA to integrate Excel data into Word document through VLookup function

[mnetzel]

Hello,

I am trying to pull data from multiple Excel 2013 workbooks into a Word 2013 document that I cannot simply copy and paste a link from Excel into Word. I tried researching this on other articles and threads, but they seem to only include portions of what I am looking to accomplish, so please excuse if the code appears messy or contradictory.

What I am looking to do is enter a customer ID (sellerID) into an input box, which would be the 'lookup_value' in the VLookup function. The 'Model' workbook and 'Parent' worksheet is where the 'table_array' is located. I have inserted ActiveX labels into the Word document where I would like the data to appear. In the code below, I want the VLookup to find the customer's region and insert into the 'Region' label.

I am getting a 1004 Error - Unable to get the VLookup property of the WorksheetFunction class. I used the object browser and it looks like the Excel.Application.WorksheetFunction.VLookup are the correct classes. What am I missing?

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exModel As Excel.Workbook
Dim wkshtParent As Excel.WorksheetDim sellerID As String

    sellerID = InputBox("Please enter the Seller ID", "Input")
    Set exModel = GetObject("R:\05-2017 Model.xlsx")
    Set wkshtParent = exModel.Worksheets("Parent")
   
    ThisDocument.Region.Caption = objExcel.WorksheetFunction.VLookup(sellerID, wkshtParent.Range("B:G"), 4, False)
    
    exModel.Close
    
    Set exModel = Nothing

End Sub

Please advise. Any feedback on how to clean up the code or make the process more efficient is welcomed, as well.

Thank you!