Exporting MS Access recordsets to multiple worksheets/tabs in Excel results in Read-Only files Using VBA

Question

I am trying to export six recordsets generated by a Do-Loop to six specific tabs in a single MS Excel workbook using VBA. Instead of updating the single tabs, however, the code creates six open iterations of the workbook with only the first being editable, the remainder read-only. The recordsets are successfully exported into the correct tab in the desired format.

Function ExportRecordset2XLS2(ByVal rs As DAO.Recordset, strSheetName)
Dim xls As Object
Dim xlwb As Object
Dim xlws As Object
Dim fld As DAO.Field
Dim strPath As String07
Dim strTitleRange,strHeaderRange, strBodyRange as String

On Error GoTo err_handler

strPath = "C:\Database\Roster.xlsx"
Set xls = CreateObject("Excel.Application")
Set xlwb = xls.Workbooks.Open(strPath)

xls.Visible = False
xls.ScreenUpdating = False
Set xlws = xlwb.Worksheets(strSheetName)
xlws.Activate

'Define ranges for formatting
    intFields = rs.Fields.Count
    intRows = rs.RecordCount
    strTitleRange = "A1:" & Chr(64 + intFields) & "1"
    strHeaderRange = "A2:" & Chr(64 + intFields) & "2"
    strBodyRange = "A3:" & Chr(64 + intFields) & (intRows + 2)

'Build TITLE Row
    xlws.Range("A1").Select 
    xls.ActiveCell = Format(Now(), "YYYY") & " Roster (" & strSheetName & ")"

'Build HEADER Row
    xlws.Range("A2").Select

For Each fld In rs.Fields
    xls.ActiveCell = fld.Name
    xls.ActiveCell.Offset(0, 1).Select
Next

rs.MoveFirst

'Paste Recordset into Worksheet(strSheetName) starting in A3
    xlws.Range("A3").CopyFromRecordset rs

On Error Resume Next
xls.Visible = True   'Make excel visible to the user
Set rs = Nothing
Set xlws = Nothing
Set xlwb = Nothing
xls.ScreenUpdating = True
Set xls = Nothing
xls.Quit

Exit Function

err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function

I suspect the problem revolves around how the function opens the .xlsx file for editing; I have tried programmatically closing the active worksheet and/or workbook in various ways and sequences to no effect. I could presumably insert a break into the code that generates the recordset to allow MS Excel to open then close, before repeating the process with the next tab, but there must be a more elegant way.

Image of multiple iterations in Excel

** As a side note, I did post this question also to answers.microsoft.com before finding this forum. Sorry. **

Thanks in advance, Erik

Answer 1

As expected, this turned out to be series of small issues that resulted in MS Excel holding the workbook file in read-only status after the function would error out. SOlved after scrutinizing each line of code to find individual lines that were failing.