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

score 1 · Accepted Answer · answered Apr 04 '16 at 22:20

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....

answered Apr 04 '16 at 22:20

SierraOscar

17,507
6
40
68

I ended the with statement and tried... xlApp.Workbooks.Worksheets.Cells.Find(...) To No Avail. Any further guidance on this? – PocketLoan Apr 04 '16 at 22:53
1

Again, `Workbooks` and `Worksheets` are **collections** you need to specify an index. Look at how I've used `Worksheets(1)` – SierraOscar Apr 05 '16 at 06:01
Set wb = .Worksheets(1).Cells.Find("End") does the trick after declaring wb as an object. Now if I could figure out how to Activate the Found Cell...is there documentation on this? – PocketLoan Apr 05 '16 at 18:56
1

`wb.Activate` - although there is [no need to use `.Select` or `.Activate` in VBA](http://stackoverflow.com/a/10718179/4240221) – SierraOscar Apr 05 '16 at 19:13
"wb.activate" gets me Run-time error 1004: The activate method of the Range class failed. wb,copy works, but I really want to copy rows 14 to the row where "END" was found -1, so if end was on row 16, rows 14 and 15 would be copied. I can ask a new question if this is too far afield. Thanks for the link on that advice on your comment just now. – PocketLoan Apr 05 '16 at 19:30

Searching an Excel Worksheet from Outlook VBA

1 Answers1