Not overwriting previous data when writing queries to Excel (1 Viewer)

[kvar]

I have a set of queries that need to be exported to an existing Excel file to the appropriate worksheets and within a date range that the user enters on the form.
That part all seems to work fine, the correct data goes to the correct sheets, etc. The queries themselves, if opened while the form is open, show exactly what they are supposed to.
However, when I go for a second run through with the same query to export selected, and say a different date range, it doesn't seem to be overwriting the existing data that is already on the sheet. If I do an export with a wide date range, then run again with a smaller date range, I should have less information on the worksheet, but it doesn't seem to overwrite at all. I'm sure this is probably a really silly small piece of code that I'm drawing a blank on right now so if someone could point me in the right direction I'd be very grateful!

Sidenote: Since this is an existing Excel file with multiple sheets, I don't want the whole thing to be overwritten just because someone does an export of one query, only that sheet should be overwritten. Unless of course they select the "All" option, then all of them would be overwritten.

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim fld As Field
Dim intColCount As Integer
Dim intColCount2 As Integer
Dim intColCount3 As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim ExportFile As String
Set xlApp = New Excel.Application
ExportFile = "C:\Temp\Export.xlsx"
Set xlBook = xlApp.Workbooks.Open(ExportFile)
    
'On Error GoTo ErrorHandler
'DoCmd.SetWarnings False
    
Set db = CurrentDb()
    
Select Case Me.Form_Selector
    Case "Educational Coach Evaluation"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Educational_Coach_Evaluation_qry", ExportFile, True, "Coach_Evaluation"
        Set xlSheet = xlBook.Worksheets("Coach_Evaluation")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET Educational_Coach_Eval = Date() ;"
        Me.Requery
        
    Case "Faculty Course Evaluation"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Course_Evaluation_qry", ExportFile, True, "Faculty Evaluation"
        Set xlSheet = xlBook.Worksheets("Faculty_Evaluation")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET Faculty_Course_Eval = Date() ;"
        Me.Requery
        
    Case "Nowicki-Strickland LOC Survey"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NS_LOC_Survey_qry", ExportFile, True, "Nowicki-Strickland"
        Set xlSheet = xlBook.Worksheets("Nowicki_Strickland")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET NS_LOC_Survey = Date() ;"
        Me.Requery
    
    Case "AIR Self-Determination Scale"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_SelfDetermination_Scale_qry", ExportFile, True, "AIR Self"
        Set xlSheet = xlBook.Worksheets("AIR_Self")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET AIR_Self = Date() ;"
        Me.Requery
        
    Case "AIR - Parent"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_Parent_qry", ExportFile, True, "AIR Parent"
        Set xlSheet = xlBook.Worksheets("AIR_Parent")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET AIR_Parent = Date() ;"
        Me.Requery
        
    Case "AIR - Educator"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_Educator_qry", ExportFile, True, "AIR Educator"
        Set xlSheet = xlBook.Worksheets("AIR_Educator")
        xlSheet.Activate
        DoCmd.RunSQL "UPDATE Export_tbl SET AIR_Educator = Date() ;"
        Me.Requery
        
    Case "Adaptive Behavior Assessment System"
        Set qdf1 = db.QueryDefs("ABAS_qry")
        Set qdf2 = db.QueryDefs("ABAS2_qry")
        qdf1.Parameters(0) = Forms!Export_frm!Begin_Date_txt
        qdf1.Parameters(1) = Forms!Export_frm!End_Date_txt
        qdf2.Parameters(0) = Forms!Export_frm!Begin_Date_txt
        qdf2.Parameters(1) = Forms!Export_frm!End_Date_txt
                                                                                                                                
        Set rst1 = qdf1.OpenRecordset
        Set rst2 = qdf2.OpenRecordset
        Set xlSheet = xlBook.Worksheets("ABAS")
          xlSheet.Activate
        intColCount = 1
        
            For Each fld In rst1.Fields
                xlSheet.Cells(1, intColCount).Value = fld.Name
                intColCount = intColCount + 1
            Next fld
    
            intColCount2 = intColCount
            'Send recordset to worksheet.
            xlSheet.Range("A2").CopyFromRecordset rst1
        
            For Each fld In rst2.Fields
                xlSheet.Cells(1, intColCount).Value = fld.Name
                intColCount = intColCount + 1
            Next fld
    
            intColCount3 = intColCount
            'Send second recordset to worksheet.
            xlSheet.Cells(2, intColCount2).CopyFromRecordset rst2
        
            DoCmd.RunSQL "UPDATE Export_tbl SET ABAS = Date() ;"
            Me.Requery
            
    Case "All"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Educational_Coach_Evaluation_qry", ExportFile, True, "Coach_Evaluation"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Course_Evaluation_qry", ExportFile, True, "Faculty Evaluation"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NS_LOC_Survey_qry", ExportFile, True, "Nowicki-Strickland"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_SelfDetermination_Scale_qry", ExportFile, True, "AIR Self"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_Parent_qry", ExportFile, True, "AIR Parent"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AIR_Educator_qry", ExportFile, True, "AIR Educator"
        Set qdf1 = db.QueryDefs("ABAS_qry")
        Set qdf2 = db.QueryDefs("ABAS2_qry")
        qdf1.Parameters(0) = Forms!Export_frm!Begin_Date_txt
        qdf1.Parameters(1) = Forms!Export_frm!End_Date_txt
        qdf2.Parameters(0) = Forms!Export_frm!Begin_Date_txt
        qdf2.Parameters(1) = Forms!Export_frm!End_Date_txt
                                                                                                                                
        Set rst1 = qdf1.OpenRecordset
        Set rst2 = qdf2.OpenRecordset
        Set xlSheet = xlBook.Worksheets("ABAS")
          xlSheet.Activate
        intColCount = 1
        
            For Each fld In rst1.Fields
                xlSheet.Cells(1, intColCount).Value = fld.Name
                intColCount = intColCount + 1
            Next fld
    
            intColCount2 = intColCount
            'Send recordset to worksheet.
            xlSheet.Range("A2").CopyFromRecordset rst1
        
            For Each fld In rst2.Fields
                xlSheet.Cells(1, intColCount).Value = fld.Name
                intColCount = intColCount + 1
            Next fld
    
            intColCount3 = intColCount
            'Send second recordset to worksheet.
            xlSheet.Cells(2, intColCount2).CopyFromRecordset rst2
        
            DoCmd.RunSQL "UPDATE Export_tbl SET All_forms = Date() ;"
            Me.Requery
            
    End Select
    
   xlApp.Visible = True
   Set xlSheet = Nothing
   Set xlBook = Nothing
   Set rst1 = Nothing
   Set rst2 = Nothing
ErrorHandler:   'error handling routine
            MsgBox Err.Number & " " & Error(Err.Number)
End Sub

 

[kvar]

Update: It actually does work for all of the Cases where it is just running a DoCmd.TransferSpreadsheet with a query.
However for the Case that I had to code to use the recordsets, Adaptive Behavior Assessment, it does not work. The same would be true also in the case of "All" for only that particular worksheet.