AndreaM
10-24-2017, 07:13 AM
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
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