Code vba export access vers excel [Résolu]

Private Sub TransfertExportExcel_Click() 
Dim xlapp As Object 
Dim acapp As Object 
Dim query As Object 
Dim XLCnx As ADODB.Connection
Dim XLRst As ADODB.Recordset
Dim monClasseur As String
Dim maFeuille As String

On Error Resume Next

monClasseur = "chemin"  'Ton chemin d'accès (ex: C:\Temp\MonClasseur.xls)
MaFeuille = "absences"

Set xlapp = CreateObject("Excel.Application") 
Set acapp = CurrentDb() 
Set query = acapp.OpenRecordset("export_absences") 

'Connection au classeur Excel
    Set XLCnx = New ADODB.Connection
    XLCnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & NomClasseur & ";" & _
                   "Extended Properties=""Excel 8.0;HDR=NO;"""

'Ouverture du recordset
Set XLRst = New ADODB.Recordset
    XLRst.Open "Select * from " & maFeuille, XLCnx, adOpenKeyset, adLockOptimistic
        
    'transfert les données Access vers le classeur Excel
    Do While Not (query .EOF)
            XLRst.AddNew
                For j = 0 To XLRst.Fields.Count - 1
                    XLRst.Fields(j) = query.Fields(j).Value
                Next j
            XLRst.Update
            query.MoveNext
    Loop

XLCnx.Close
XLRst.Close
query.Close

Set xlapp = Nothing 
Set acapp = Nothing
Set query = Nothing
Set XLCnx = Noting
Set XLRst = Nothing

If Err.Number <> 0 Then
   MsgBox Err.Number & " - " & Err.Description, vbCritical, "Erreur !!!"
Else
   MsgBox "export réalisé avec succès =)", vbInformation, "" 
End If

 End Sub