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.
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.
Code:
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