How to search fast in Excel from Word via VBA?

[gtaborda]

Hi there

i have some macros in Word which

1. opens Excel sheet from Word (hidden)
2. Searches through sheet for string
3. if found it returns the values of cells in same row
4. Those values are then used in Word


I am currently using a loop to goo through each row.
This works well but...it's too slow, specially when the excel sheet has >500 rows

Is there a faster way to seek and collect data from excel and use it in Word ?

thanks in advance for your help

 

[SkipVought]

Hi,

How about the Find & FindNext Methods?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[gtaborda]

Hi,

Yes, that's what I am looking for.

I've tried with
Set aRange = xl.ActiveSheet.Find(What:=txtclientName, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

Somehow it does't work (it locks Excel)

So I am totallly lost. I don't know where to go from here,

I've looked a lot on the internet but I am unable to find anything about it.




'currentcolumn = ActiveCell.Column
' currentrow = ActiveCell.Row

 

[PHV]

You may try to replace this:
After:=ActiveCell
with this:
After:=xl.ActiveCell

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Avoid Activate and Select...

Set aRange = xl.[b]Sheets("YourSheetName").Cells[/b].Find(What:=txtclientName, After:=xl.[b]Sheets("YourSheetName").Cells.[A1][/b], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

on subsequent searches...

Set aRange = xl.[b]Sheets("YourSheetName").Cells[/b].Find(What:=txtclientName, After:=[b]aRange[/b], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[gtaborda]

Hi

I've tried SkipVought's code with no luck

I get an error 438, "the object doesn't admit this property or method"

Same if I use xl.ActiveCell as per PHV comments

 

[macropod]

Hi gtaborda,

Perhaps it's time to post the relevant portions of your code ...

[MS MVP - Word]

 

[gtaborda]

Find the code so far...

This code runs when a Form is opened (from button on Toolbar)

The form has several fields, which I need to get filled after searching on CLIENTSDATA.XLS file (which is hidden)

If "txtclientName" is found (this variable comes from Word, then the 5 cells next to it in Excel need to automatically be added to the corresponding fields on the Word Form.

When the code gets to "Set aRange... " the code stops running for over 1 minute and finally it gives me error 438

I hope that makes sense

Public Sub getallclientsdata()


Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open (txtBlankDirectory & "\CLIENTSDATA.xls")
Set sh = xl.Worksheets("VARIABLES")
xl.Visible = False

Set aRange = xl.Sheets("VARIABLES").Cells.Find(What:=txtclientName, After:=xl.Sheets("VARIABLES").Cells.[A1], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)


' THIS IS HOW I DO IT NOW, WORKS BUT IT IS TOO SLOW
For l = 2 To 3000
If txtclientName = sh.Range("A" & l).Value then
txtcode = sh.Range(("C"& l).Value
txtBAFCAF = sh.Range(("D" & l).Value
txtcombineprint = sh.Range(("E" & l).Value
txtPSS = sh.Range(("F" & l).Value
txtCAFon = sh.Range(("G" & l).Value
l = 3000

else
end if
next l


End Sub

 

[SkipVought]

You set the sheet and then you ignore the sheet object????

Set sh = xl.Worksheets("VARIABLES")
xl.Visible = False

Set aRange = sh.Cells.Find(What:=txtclientName, After:=sh.Cells.[A1], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[gtaborda]

Hi
Sorry but my knowledge in
sheet objects is limited

I've used Set aRange = sh.Cells.Find(What:=txtclientName, After:=sh.Cells.[A1], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

however I still get the same error, although it happens instantly (intead of before that the system got locked for 1+ minutes)

438, "the object doesn't admit this property or method

 

[SkipVought]

The you have to use the Watch Window to discover what OBJECT is burping.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

Then you have to use the Watch Window to discover what OBJECT is burping.

Sorry

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[combo]

Do you have a reference to Ms Excel library set? If not, all xlConstants are not recognised.

combo

 

[gtaborda]

Yes combo, I do have reference to Excel ticked.

I'll investigate further

 

[combo]

What happens after:

dim xl as excel.application
dim wb as excel.workbook
dim sh as excel worksheet
dim aRange as excel range
Set xl = CreateObject("Excel.Application")
set wb=xl.Workbooks.Open (txtBlankDirectory & "\CLIENTSDATA.xls")
...
' xl.Visible = False (do not hide excel, anyway, it should be hidden)
msgbox sh.usedrange.address ' what is the answer?
Set aRange = sh.usedrange.Find(What:=txtclientName, After:=xl.Sheets("VARIABLES").Cells.[A1], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
...


combo

 

[gtaborda]

on msgbox sh.usedrange.address I get $A$1:$AP$3962

 

[combo]

And still error 438? Is the aRange dimmed? If so, it should be as Excel.Range, otherwise it will be assumed to be Word.Range.

combo

 

[gtaborda]

cOMBO, I didn`t have it dimmed, now I do (Dim aRange As Excel.Range) however the error still there (438)

nuts

Do you have any example you could perhaps send me (that works) then I can just copy it/adapt it to my application?

thanks

 

[fumei]

combo asked:

msgbox sh.usedrange.address ' what is the answer?

faq219-2884

Gerry
My paintings and sculpture

 

[combo]

With some code from excel vba help:

Sub Test()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim xlRange As Excel.Range
Dim txtToFind As String, txtFirstFoundAddress As String

txtToFind = "N"
Set xlApp = New Excel.Application
' xlApp.Visible = True
Set xlWbk = xlApp.Workbooks.Open("D:\x\test.xls")
Set xlWks = xlWbk.Worksheets("test")
With xlWks.UsedRange
    Set xlRange = .Find(What:=txtToFind, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not xlRange Is Nothing Then
        txtFirstFoundAddress = xlRange.Address
        MsgBox txtFirstFoundAddress
        Do
            Set xlRange = .FindNext(xlRange)
            If xlRange.Address <> txtFirstFoundAddress Then
                MsgBox xlRange.Address
            End If
        Loop While xlRange.Address <> txtFirstFoundAddress
    End If
End With
xlApp.Quit
End Sub

combo