Can't Write to Excel File When Recordset is Open

[Kluaoha]

I am working towards exporting Access data into Excel by means of a Recordset using VBA. For a while I was struggling to figure out why nothing was being written, until I started commenting out sections and ultimately got down to the situation here:

Private Sub ExportButton_Click()
    Dim XLapp As Excel.Application
    Dim XLwbk As Workbook
    Dim XLsht As Worksheet
    Dim rs As Recordset
    
    On Error GoTo ExportError
    Set XLapp = New Excel.Application
    Set XLwbk = XLapp.Workbooks.Open(CurrentProject.Path & "\MyExcelFile.xlsx")
    Set XLsht = XLwbk.Worksheets(1)
    Set rs = CurrentDb.OpenRecordset("MyQuery", dbOpenSnapshot) 'THIS LINE
    With XLsht
        .range("A5").value = 2
    End With

    If MsgBox("Export finished successfully. Open file?", vbDefaultButton2 + vbQuestion + vbYesNo) = vbYes Then
        XLapp.Visible = True
    End If    

ExportError:
    Set XLapp = Nothing: Set XLwbk = Nothing: Set XLsht = Nothing: Set rs = Nothing
End Sub

Nothing is written to the Excel file with the above code, but commenting out THIS LINE makes it work. So it seems like the recordset is messing something up somehow. Any suggestions?
Thanks!

[pbaldy]

The way you handle errors will make it fail silently. You might try more traditional error handling:

http://www.baldyweb.com/ErrorTrap.htm

or temporarily comment out the "On Error..." line and run the code. The recordset is failing for some reason.

[Kluaoha]

Thanks, I was getting error 13 (Type mismatch) for not specifying it as DAO.Recordset.

[pbaldy]

No problem, happy to help.