Hi,

I'm trying to export data to an Excel template and saving it to the right location. Everything works fine, except for the exportation itself. Opening the template, saving it to the location, changing sheet names,... all works.

Here is the code I'm using:

Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim intAnswer As Integer
Dim dtDate As Date
Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer
Dim sTemplate As String
Dim intColonne As Integer
Dim intLigne As Integer
Dim fld As DAO.Field

If IsNull(Me.cboEinddatum) Or Me.cboEinddatum = "" Then
    MsgBox "Gelieve een einddatum in te vullen." & vbCrLf & vbCrLf & _
        "Veuillez entrer une date de fin.", vbExclamation, "CobelAdmin"
    Me.cboEinddatum.SetFocus
    Exit Sub
Else
    dtDate = Me.cboEinddatum
    intDay = DatePart("d", dtDate, vbMonday, vbFirstFourDays)
    intMonth = DatePart("m", dtDate, vbMonday, vbFirstFourDays)
    intYear = DatePart("yyyy", dtDate, vbMonday, vbFirstFourDays)
End If

        sTemplate = Application.CurrentProject.Path & "\Templates\Archief sleutels.xlt"
        'open the query qryARCHIVESleutels
        Set db = Application.CurrentDb
        sSQL = "SELECT tblOpvolgingSleutels.Autonumber, tblOpvolgingSleutels.DatumUit, " & _
                    "tblOpvolgingSleutels.UurUit, tblOpvolgingSleutels.NaamSleutel, " & _
                    "tblOpvolgingSleutels.Firma, tblOpvolgingSleutels.NaamUitlener, " & _
                    "tblOpvolgingSleutels.GsmUitlener, tblOpvolgingSleutels.Centralist, " & _
                    "tblOpvolgingSleutels.DatumIn, tblOpvolgingSleutels.UurIn, " & _
                    "tblOpvolgingSleutels.InvoerDoor, tblOpvolgingSleutels.InvoerOp, " & _
                    "tblOpvolgingSleutels.WijzigingDoor, tblOpvolgingSleutels.WijzigingOp " & _
                "FROM tblOpvolgingSleutels " & _
                "WHERE (((tblOpvolgingSleutels.DatumUit) <=" & dtDate & ") And " & _
                    "((tblOpvolgingSleutels.Centralist) Is Not Null) And " & _
                    "((tblOpvolgingSleutels.DatumIn) Is Not Null) And " & _
                    "((tblOpvolgingSleutels.UurIn) Is Not Null)) " & _
                "ORDER BY tblOpvolgingSleutels.DatumUit, tblOpvolgingSleutels.UurUit, " & _
                    "tblOpvolgingSleutels.NaamSleutel;"
        Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
        'start excel
        Set xl = New Excel.Application
        xl.Visible = False
        With xl
            'open the existing template
            Set wbk = .Workbooks.Open(sTemplate)
            'Change name of first sheet
            wbk.Sheets(1).Name = "Gegevens tem " & intYear & "_" & intMonth & "_" & intDay
            With wbk.Sheets(1)
                'transfer the data
                intLigne = 3
                While Not rst.EOF
                    intColonne = 1
                    For Each fld In rst.Fields
                        .Cells(intLigne, intColonne) = fld.Value
                        intColonne = intColonne + 1
                    Next
                    'next record
                    rst.MoveNext
                    intLigne = intLigne + 1
                Wend
            End With
            'close workbook and save it
            wbk.SaveAs Application.CurrentProject.Path & "\Archives\Archief sleutels tem " & _
                intYear & "_" & intMonth & "_" & intDay & ".xls"
            wbk.Close
        End With
        'close recordset and connection
        xl.Quit
        Set xl = Nothing
        rst.Close
        Set rst = Nothing
        Set db = Nothing

I have put the part of the code that is not working in red.
The exportation should start on row 3 in Excel, because row 1 and 2 are already in use on the template...
However, no data gets exported, the query is fine, I tested it.

Does anyone have any ideas why it is not working?

Thanks