Solved Access VBA freezes after formatting external spreadsheet

[pooldead]

I am creating spreadsheets from a recordset as shown below.

                Do While Not rs1.EOF
                    sqlStr = "SELECT Transsend_M3_Var.APP_USERNAME, Transsend_M3_Var.Last_Name, Transsend_M3_Var.First_Name, Transsend_M3_Var.Job_Code, Transsend_M3_Var.Job_Title, Transsend_M3_Var.Dept_ID, Transsend_M3_Var.Dept_Title, Transsend_M3_Var.Roles " & _
                             "FROM Transsend_M3_Var " & _
                             "WHERE ((Transsend_M3_Var.Manager_ID)='" & rs1.Fields("Manager_ID") & "');"
                    Set rs2 = CurrentDb.OpenRecordset(sqlStr)
                    If (rs2.RecordCount = 0) Then
                        MsgBox ("No records found on Transsend_M3_Var")
                        Exit Do
                    Else
                        rs2.MoveLast
                        rs2.MoveFirst
                        
                        Dim rsExport As DAO.QueryDef
                        Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", sqlStr)
                        
                        fileName = filePath & rs1.Fields("Manager_ID") & ".xlsx"
                        
                        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "myExportQueryDef", fileName, True
                        
                        Tools.formatWorkbooks (fileName)
                        
                        CurrentDb.QueryDefs.Delete rsExport.Name
                    End If
                    rs1.MoveNext
                Loop

I have a function I am running, below, that opens the spreadsheet that was created, formats it, and then should close and allow the loop to continue.

Public Function formatWorkbooks(fileName As String)

    Dim xlApp As Excel.Application, xlWork As Excel.Workbook, xlSheet1 As Excel.Worksheet

    Set xlApp = New Excel.Application
        'xlApp.Visible = False
    
    Set xlWork = xlApp.Workbooks.Open(fileName)
        'xlWork.Windows(1).Visible = False
    
    Set xlSheet1 = xlWork.Worksheets(1)
    With xlSheet1
        .Range("I1").FormulaR1C1 = "APPROVE/DENY"
        .Range("A1", "I1").Columns.AutoFit
        .Range("A1", "I1").Characters.Font.Bold = True
    End With
    
    xlWork.Close True
    Set xlWork = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Function

The problem I am having is that code seems to run fine, but Access gets tied up and won't continue past the loop. It just freezes.

 

[theDBguy]

Hi. Just to clarify, are you saying you are getting multiple properly formatted Excel files matching the records in your database, but the code hangs up after it's done? How far down in the code does it go before it stops?

 

[pooldead]

Hi. Just to clarify, are you saying you are getting multiple properly formatted Excel files matching the records in your database, but the code hangs up after it's done? How far down in the code does it go before it stops?

Yes, the output Excel files do get formatted correctly all the way through the looped recordset, but it seems like Access hangs at the end. I have some code after that will create emails and that piece won't run. There are no error messages that pop-up either.

Everything does work fine if I take the formatting piece out.

 

[theDBguy]

Yes, the output Excel files do get formatted correctly all the way through the looped recordset, but it seems like Access hangs at the end. I have some code after that will create emails and that piece won't run. There are no error messages that pop-up either.

Everything does work fine if I take the formatting piece out.

I mean, if you put a breakpoint on the first line of the email code, does it stop?

 

[pooldead]

It did. And I'll have to apologize. Apparently, due to the number of spreadsheets that are created, it takes 3 minutes to cycle through them all. So I guess I don't have an issue except for being impatient (which was already a known problem).

Thanks @theDBguy

 

[theDBguy]

It did. And I'll have to apologize. Apparently, due to the number of spreadsheets that are created, it takes 3 minutes to cycle through them all. So I guess I don't have an issue except for being impatient (which was already a known problem).

Thanks @theDBguy

Okay, just remember, creating files takes time because mechanical parts are involved. Good luck with your project.

 

[isladogs]

Following on from a previous thread, it sounds like a progress bar might be helpful here