I have the following code that is included in a function that runs through a recordset of users. It executes perfectly for the first record. When it gets to the second record, the code runs the ".Range("A2").CopyFromRecordset rs1" then skips back to the main function and doesn't finish out the rest below. No errors occur when this happens.
Code:
Public Function excelExport(qryData As String, filePath As String)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset(qryData)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
With xlSheet
.Name = "Review"
.Range("A1").FormulaR1C1 = "Agency"
.Range("B1").FormulaR1C1 = "Username"
.Range("C1").FormulaR1C1 = "Group Name"
.Range("D1").FormulaR1C1 = "First Name"
.Range("E1").FormulaR1C1 = "Last Name"
.Range("F1").FormulaR1C1 = "Account Status"
.Range("G1").FormulaR1C1 = "APPROVE/DENY"
.Range("H1").FormulaR1C1 = "Business Justification"
.Range("A2").CopyFromRecordset rs1
.ListObjects.Add(xlSrcRange, _
Range("$A$1:$H$" & .Range("A" & .Rows.Count).End(xlUp).Row), , _
xlYes).Name = "Table1"
.ListObjects("Table1").TableStyle = "TableStyleMedium9"
.Range("Table1[#Headers]").AutoFilter
.Range("A2").Select
End With
Set xlSheet = Nothing
xlBook.SaveAs filePath, 51
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Function