Search an Excel Column from Access VBA - Type Mismatch

Question

My issue occurs on the following line (the error is a Type Mismatch):

RowNo = xlSheet1.Cells.Find(What:="SummaryType", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

Background: I am building a database which loads a text file, splits it into a number of different pieces, combines some pieces to make new tables, then exports the new tables to excel and does some formatting.

I use a 'For Each' loop to go through my access tables. When certain tables are identified some other code runs that creates new tables (code not shown). Once a new table is created is gets exported to excel and formatted. This is where the error occurs. The first loop works fine, it is on the second loop that the code goes to error when trying to find the row that contains "SummaryType" in Column A. The error is Run-Time Error 13 - Type Mismatch.

Code:

Dim outputFileName               As String
Dim xl                           As Excel.Application
Dim xlBook                       As Excel.Workbook
Dim xlSheet1                     As Excel.Worksheet
Dim RptMnth                      As String
Dim RptYear                      As Long
Dim RptName                      As String

outputFileName = "C:\Users\UserID\Desktop\Reports\" & RptName & "_" & RptMnth & "_" & RptYear & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Report", outputFileName, True

Set xl = New Excel.Application
Set xlBook = xl.Workbooks.Open(outputFileName)
xl.Visible = True

Set xlSheet1 = xlBook.Worksheets(1)

With xlSheet1
    .Columns("A").Delete Shift:=xlToLeft
    .Rows(1).Delete Shift:=xlUp

    .Range("A1:J1").Interior.Color = RGB(191, 191, 191)
    .Range("A1:J1").Borders.Weight = xlThin
    .Range("A1:J100").Font.Name = "Calibri (Body)"
    .Range("A1:J100").Font.Size = 11
    .Range("A1:J1").HorizontalAlignment = xlCenter

    RowNo = xlSheet1.Cells.Find(What:="SummaryType", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

    .Range("A" & RowNo & ":F" & RowNo).Interior.Color = RGB(191, 191, 191)
    .Range("A" & RowNo & ":F" & RowNo).Borders.Weight = xlThin
    .Range("A" & RowNo & ":F" & RowNo).HorizontalAlignment = xlCenter
    .Range("A1:J100").Cells.Columns.AutoFit
End With

xl.DisplayAlerts = False

xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close

xl.DisplayAlerts = True

Set xlSheet1 = Nothing
Set xlBook = Nothing
Set xl = Nothing

Answer 1

I don't believe that the ActiveCell property is being identified correctly. This is not within the Excel VBA where such properties are automatic.

RowNo = .Cells.Find(What:="SummaryType", After:=xl.ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

Take off the redundant xlSheet1 and make xl.ActiveCell part of the Excel.Application that references the parent worksheet.

Alternately, any cell will do (e.g. .Cells(1)) or you could simply omit the After:=... parameter.