Code Execution Skips with No Error (1 Viewer)

pooldead

Registered User.
Local time
Today, 14:35
Joined
Sep 4, 2019
Messages
132
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:35
Joined
Oct 29, 2018
Messages
19,004
Hi. Have you stepped through the entire procedure? Just curious... Don't think we can tell right away what's wrong if we can't see it in action.
 

pooldead

Registered User.
Local time
Today, 14:35
Joined
Sep 4, 2019
Messages
132
I have. It hits that copyfromrecordset line, then on the next step just goes back to the main function. I've never seen it behave like this before, not without an error along with it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:35
Joined
Oct 29, 2018
Messages
19,004
I have. It hits that copyfromrecordset line, then on the next step just goes back to the main function. I've never seen it behave like this before, not without an error along with it.
Hi. Part of stepping through a procedure is examining the contents of your variables and objects. Did you happen to notice anything unusual with your objects when it does that?
 

pooldead

Registered User.
Local time
Today, 14:35
Joined
Sep 4, 2019
Messages
132
I did not. All the variables/objects were assigned appropriately.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:35
Joined
Oct 29, 2018
Messages
19,004
I did not. All the variables/objects were assigned appropriately.
Assignment was not the issue - it's their values. So, you need to pay attention if they contain what you expect them to have for the code to work properly. Otherwise, if there's bad data, the variables may get unexpected values, and the code will branch off to a path you didn't expect.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:35
Joined
Sep 21, 2011
Messages
10,603
What do you mean 'It executes perfectly for the first record'?
CopyfromRecordset processes all records in one go?
 

pooldead

Registered User.
Local time
Today, 14:35
Joined
Sep 4, 2019
Messages
132
@Gasman - yes, the first time through the function, the copyfromrecordset dumps the data into excel correctly. Stepping through a little more carefully, it looks like it has some kind of issue with the .listobjects.add portion from the 2nd run and on. I'm trying to look at why it doesn't like that.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:35
Joined
Sep 21, 2011
Messages
10,603
Is that because you are not changing the table name for subsequent recordsets?
@Gasman - yes, the first time through the function, the copyfromrecordset dumps the data into excel correctly. Stepping through a little more carefully, it looks like it has some kind of issue with the .listobjects.add portion from the 2nd run and on. I'm trying to look at why it doesn't like that.
 

Users who are viewing this thread

Top Bottom