Finding data in an Excel file

[jille]

Hi,

I'm trying to find data quickly in an Excel file from a Word userform.

I currently have it working but it is very slow and am exploring faster
methods. Currently I have the macro going to a specific cell, and then doing
a loop downwards until it finds the correct string. I would prefer to use
either:

- the Find method on a range but can't seem to use the Cells.Find method in
Word

- or the Match Worksheetfunction but can't seem to specify the range (the
documentation I found on this topic identifies a need to set the range as an
object...I did this but it's currently not being recognized by Word). I am
currently getting a 'type mismatch' error on the line below. I suspect the
error is caused by the fact that the range is an Excel range, not one in Word:

myRange = .Application.Worksheets("Sheet1").Range("k1:m300")

Here is the code from beginning to end:

Dim test, results, answer
test = "CR 03"

Dim myRange As Range
Dim SalaryFile As Object
Set SalaryFile = GetObject("c:\Project\Pay Rate table v3.xls")
SalaryFileName = "CRA Pay Rate table v3.xls"

'*************OPEN EXCEL****************
With SalaryFile
.Application.Visible = True
.Parent.Windows(SalaryFileName).Visible = True

myRange = .Application.Worksheets("Sheet1").Range("k1:m300")
answer = .Application.WorksheetFunction.Match(test, myRange)

.Save
.Close

End With
MsgBox answer
End Sub

Would really appreciate some help here!

Thanks,

jille

 

[Doug Robbins - Word MVP]

You need to use the Outlook Object model. The best place to get assistance
with that would be in microsoft.public.excel.programming.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Jille,

apart from being an Exel expert,
this one works for me,
with early binding, Excel already running,
just to show the principle:

Sub Test555()
Dim oExl As Excel.Application
Dim oWrk As Excel.Workbook
Dim oSht As Excel.Worksheet
Set oExl = GetObject(, "Excel.Application")
Set oWrk = oExl.ActiveWorkbook
Set oSht = oWrk.ActiveSheet
Dim c As Excel.Range
With oSht.Range("A1:E21")
Set c = .Find("H X M", LookIn:=xlValues)
End With

MsgBox c.Address

End Sub


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Tony Jollans]

You are not wrong. You have defined MyRange as a Word Range but you try to
set it (see below) to an Excel Range. Either define it as Object or
Excel.Range (if you have a reference set to the Excel library).

Actually you aren't setting myRange - you need to explicitly say Set MyRange
= etc.