.selection.find not working via automation

[Bill Schanks]

I have an access database that creates a report in XL via automation.
I have this code that is not working:

<<Snip>>
Set XL = New Excel.Application
With XL

.Visible = True 'debugging only
.Workbooks.Add

i = rs.Fields.Count - 1

'Header row
For x = 0 To i
.Cells(1, x + 1).Value = rs(x).Name
Next x

'Detail
.Range("A2").CopyFromRecordset rs

'format it

'Remove 'Limiter columns
On Error Resume Next
Do
.Range("1:1").Select
.Selection.Find(What:="limiter", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False,
SearchFormat:=False).EntireColumn.Delete
If Err.Number <> 0 Then Exit Do
Loop
On Error GoTo cmdOpenReport_Click_ErrHandle
<<Snip>>

The .selection.find is not finding the text 'limiter'. Howerver if I
run that section of the code in the excel immediate window it works
fine.

Any ideas?

 

[RB Smissaert]

You are doing .Cells .Range .Selection etc. but these
are properties of a worksheet not of the Excel application.
So you will have to fully qualify the worksheet and do something like this:

Dim XL As Excel.Application
Dim XLBook As Workbook
Dim XLSheet As Worksheet

Set XL = New Excel.Application
Set XLBook = XL.Workbooks.Add
Set XLSheet = XLBook.ActiveSheet

With XLSheet

For x = 0 To i
.Cells(1, x + 1).Value = rs(x).Name
Next x

etc.


RBS