Find value in Excel from Array within Word

[AndreaM]

Hi, I am trying to open up an Excel Worksheet from within Word and then Find values from an Array then update a Document Variable from an Offset of 9 Columns to the right, but keep getting an error. What I am trying to do is update TextBox values from a Word Userform to values found in an Excel Spreadsheet by searching for a code and returning the value in the 9th column of that found value.



Private Sub UpdatePricing_Click()
'Dim myForm As UserForm1
Dim xlApp As Object
Dim xlbook As Object
Dim bStartApp As Boolean
On Error Resume Next
'Set myForm = New UserForm1
Set xlApp = GetObject(, "Excel.Application")
If Err Then
bStartApp = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
With xlApp
Set xlbook = .Workbooks.Open("Pricelist 2017.xlsx")


Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long


MyArr = Array("be57ff4c-100c-4f1f-b82d-f1c5ab63a665", "91fd106f-4b2c-4938-95ac-f54f74e9a239", "796b6b5f-613c-4e24-a17c-eba730d49c02", "8909e28e-5832-42f4-9886-b0a5545f3645", _
"a044b16a-1861-4308-8086-a3a3b506fac2", "195416c1-3447-423a-b37b-ee59a99a19c4", "2f707c7c-2433-49a5-a437-9ca7cf40d3eb", "ff7a4f5b-4973-4241-8c43-80f2be39311d", _
"69c67983-cf78-4102-83f6-3e5fd246864f", "b4d4b7f4-4089-43b6-9c44-de97b760fb11", "d3bca131-4772-47bc-9c2e-e4040f82268c", "90d3615e-aa96-478e-b6ce-8eb1e9a96b4b", _
"bf1f6907-1f8e-4f05-b327-4896d1395c15", "aca0c06c-890d-4abb-83cf-bc519a2565e5", "14c61739-b45a-42c0-832c-d330972d3173")


With xlbook.Sheets("Sheet1").Range("A:J").Select


For I = LBound(MyArr) To UBound(MyArr)


Set .Rng = .Find(What:=MyArr(I), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


If Not .Rng Is Nothing Then
FirstAddress = .Rng.Address
Do
ActiveDocument.Variables("Product1") = .Rng.Offset(0, 9).value
Set .Rng = .FindNext(.Rng)
Loop While Not .Rng Is Nothing And .Rng.Address <> FirstAddress
End If
Next I
End With





xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If bStartApp Then xlApp.Quit
Set xlApp = Nothing
Me.Product1.Text = ActiveDocument.Variables("Product1").value

Set myForm = Nothing
End Sub

[Leith Ross]

Hello AndreaM,

When writing code for 2 or more applications, qualifying references is essential to prevent errors. Case in point, both Word and Excel have Range objects. Because the VBA code resides in the Word VBE, unqualified references are assumed to be part of the Word application object and it's loaded library references.


' Instead of...
Dim Rng As Range

' Use...
Dim Rng As Excel.Range


In VBA it is rare that an object must be Activated or Selected before performing an action.


' Change this line...
With xlbook.Sheets("Sheet1").Range("A:J").Select

' To this...
With xlbook.Sheets("Sheet1").Range("A:J")


The above code can be improved by limiting the range to only cells with formulae or values. Searching the entire column, including blank used cells, can slow your code a lot. Since column "J" is 9 columns to the right of column "A", is the value you are searching for in column"A" and the value you want to return in column "J"?

[AndreaM]

Thank you for the quick response. If I change Rng to Excel.Range then I get a "User-defined type not defined".

[AndreaM]

Never mind. I added the Excel Library.

[AndreaM]

Thank you so much for the assistance. It is working perfectly now.

[Leith Ross]

Hello AndreaM,

I am happy to know you figured out the problem and have the code working. Excellent troubleshooting on adding in the Excel library! Glad I could help.