Code Execution Skips with No Error (1 Viewer)

pooldead

Registered User.
Local time
Today, 06:25
Joined
Sep 4, 2019
Messages
136
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, 06:25
Joined
Oct 29, 2018
Messages
21,357
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, 06:25
Joined
Sep 4, 2019
Messages
136
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, 06:25
Joined
Oct 29, 2018
Messages
21,357
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, 06:25
Joined
Sep 4, 2019
Messages
136
I did not. All the variables/objects were assigned appropriately.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:25
Joined
Oct 29, 2018
Messages
21,357
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, 13:25
Joined
Sep 21, 2011
Messages
14,041
What do you mean 'It executes perfectly for the first record'?
CopyfromRecordset processes all records in one go?
 

pooldead

Registered User.
Local time
Today, 06:25
Joined
Sep 4, 2019
Messages
136
@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, 13:25
Joined
Sep 21, 2011
Messages
14,041
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.
 

Privateer

Registered User.
Local time
Today, 09:25
Joined
Aug 16, 2011
Messages
191
I want to put a solution out there even though it may or may not be related to the OP's problem. I received an out-of-range error on the CopyFromRecordset line of code in a procedure with error trapping. It occurred when one procedure, that was looping through forty records until end of file, called the problematic procedure to create the Excel file. I mention the looping because the code would create 22 or 23 Excel files successfully, each with three worksheets, meaning three CopyFromRecordset lines for each Excel file, and then blow up. The record sets were all DAO and being set to nothing and everything was being expunged when the code exited the procedure and went back to the calling (looping) one. Meaning each loop started with brand new variables and record-sets. The largest record set had about 3,000 records and 25 fields, so not big by any means. After some searching, I decided to put a DoEvents command right after each of the three record-sets were copied into the three Excel worksheets and one before the move-next command in the looping procedure. My thinking was the next line of code was being executed before the paste into Excel had completed. Anyway, that's all I changed, and the code looped through all forty records and created forty Excel files just fine, no errors. My theory for the OP's issue is that the function did not trap the error, blew up on the copy / paste, and bounced back to the calling procedure. Hope this helps someone.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 28, 2001
Messages
26,998
@Privateer, thanks for your contribution. Since this is an old thread, we might have trouble deciding whether the OP had originally disabled error messaging and forgot to re-enable it. Surely an untrapped error could have the effect as described. We can leave this speculation and see whether member @pooldead chooses to respond. However, this member has not posted again since this thread.
 

cheekybuddha

AWF VIP
Local time
Today, 13:25
Joined
Jul 21, 2014
Messages
2,237
Code:
' ...
                    .ListObjects.Add(xlSrcRange, _
                                        Range("$A$1:$H$" & .Range("A" & .Rows.Count).End(xlUp).Row), , _
                                        xlYes).Name = "Table1"
' ...
You have an unqualified reference to the Range object in this line.

You will end up with hanging instances of excel in your processes.

I am answering from my phone, so haven't checked whether you have more such errors in your code.

This is likely the cause of the code only appearing to run once.
 

cheekybuddha

AWF VIP
Local time
Today, 13:25
Joined
Jul 21, 2014
Messages
2,237
Try adjusting to:
Code:
' ...
                    .ListObjects.Add(xlSrcRange, _
                                        .Range("$A$1:$H$" & .Range("A" & .Rows.Count).End(xlUp).Row), , _
                                        xlYes).Name = "Table1"
' ...

Then save and close Access completely.

Re-open and re-test.
 

Users who are viewing this thread

Top Bottom