Need assistance with following code

[ceppe]

Need assistance with following code...

Word macro, loads excel then performs a find there...
But i get an error 91 on the find command... any pointers?

Sub GetEAN()
Dim res As String
Dim cur As String
Dim scount As Integer
Dim found As Integer
Dim i As Integer
Dim lRange As String
Dim t As Long

Dim oApp As Excel.Application
Dim wb As Excel.Workbook
Dim wRange As Excel.Range

Dim vEAN As String
Dim vREF As String

' default value for EAN if something is highlighted
cur = Selection.Text
res = InputBox("Enter REF code to search for...", "Get REF", cur)

If res = "" Then
Exit Sub
End If

If Tasks.Exists("Microsoft Excel&quot = True Then
Set oApp = GetObject(, "Excel.Application&quot
Else
Set oApp = CreateObject("Excel.Application&quot
End If

oApp.Visible = True

If oApp.Workbooks.Count = 0 Then
' load workbook
Set wb = oApp.Workbooks.Open(FileName:="c:\Data\Pricelists\TARIFHOME2003.xls&quot
Else
Set wb = oApp.ActiveWorkbook
End If

If wb Is Nothing Then
MsgBox "Workbook is nothing"
Exit Sub
End If

found = 0
For i = 1 To oApp.Sheets.Count
scount = 1

Set wRange = wb.Worksheets(i).Cells

t = wRange.Find(What:=res, _
Lookat:=xlWhole, _
SearchDirection:=xlByRows, _
SearchOrder:=xlNext).Activate

scount = ActiveCell.Row
If scount <> 1 Then
found = i
Exit For
End If
Next i

 

[RobBroekhuis]

You're trying to do an awful lot in just one command - you are finding a cell, activating it, and assigning its value to a variable. Problem is, when nothing is found, the find object is &quot;Nothing&quot;, which cannot be activated.

Try a few steps:
dim rFind as range
set rFind = wRange.Find(What:=res, Lookat:=xlWhole, SearchDirection:=xlByRows, SearchOrder:=xlNext)
if not (rFind is Nothing) then
t=rFind
end if

By the way, make sure that in your Word environment the xlByRows etc constants have the proper values. I've found that has given me trouble at times, even with the reference to Excel set properly.
Rob

 

[ceppe]

Thank you very much, problem solved !