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