.selection.find not working via automation
 

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?

.selection.find not working via automation
 

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


"Bill Schanks" wrote in message
oups.com...
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?