Searching an Excel Worksheet from Outlook VBA

Question

I have an Outlook Macro that opens some excel files. I want to know how I can use the Cells.find syntax and workbooks.Activate syntax in my Outlook macro.

'OUTLOOK VBA CODE here (works fine)... 
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
.Workbooks.Open ("J:\Retail Finance\Varicent\General Resources\Acting Mgr Assignment Bonus Aggregation.xlsx")
For Each x In AttachNames
    'Open the Attachments (one by one as loop iterates)
        .Workbooks.Open ("J:\Retail Finance\Varicent\General Resources\AAA\" & AttachNames(i))
    'Find the Word End, Activate the Cell that it resides in            
'####This syntax doesn't work ####
      .Worksheets.Cells.Find(What:="End", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    'Declare that row the "Endrow"
'####I'm betting this won't work either####
      endrow = ActiveCell.Row
    'Copy the range of Acting & Additional Bonuses in the file
        xlApp.Worksheet.Rows("6:" & endrow - 1).Copy
    'Activate the Aggregation File,
'####Code for activating a particular workbook####
    'find end,
        'Cells.Find(What:="End", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

Answer 1

Your With block is using xlApp and you start the erroneous line with .Worksheets

The xlApp doesn't have a .Worksheets collection so it won't work. You need to reference the workbook first.

Finally, .Worksheets is a collection - you need to specify which index (sheet) you actually want to target:

---

Dim wb As Object

Set wb = .Workbooks.Open ("J:\Retail Finance\Varicent\General Resources\AAA\" & AttachNames(i))

wb.Worksheets(1).Find("something") '// etc etc....

---