Hello,
I’m a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.
The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.
Both queries have two parametesr passed from Form1 [Start Date] and [End Date].
I’ve found several examples that I’ve tried to imitate but need you help correcting my code.
Thank you so much for your help in advance:
I’m a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.
The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.
Both queries have two parametesr passed from Form1 [Start Date] and [End Date].
I’ve found several examples that I’ve tried to imitate but need you help correcting my code.
Thank you so much for your help in advance:
Code:
Public Sub ExportToExcel()
'Step 1: Declare your variables
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim ReportDate As QueryDef
Dim TestData As QueryDef
Dim rsTestData As Recordset
Dim rsReportDate As Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim i As Integer
Dim j As Integer
'pass SQL to a variable
strSQL = "SELECT dbo_Patient.PatientId, dbo_Patient.HospitalID, dbo_Patient.lastnm, dbo_Patient.firstnm, dbo_Patient.categoryCd, dbo_Patient.IncludeMailInd, dbo_Sample.LogTime, dbo_Sample.SampleDt, dbo_Test.AssignedDt, dbo_Test.TestTypeCd, dbo_Test.TestId, dbo_Test.OrderNbr, dbo_UserTest.SoftTestCodes, dbo_UserTest.Repeats, dbo_Test.ReportableCommentsTxt"
FROM ((dbo_Patient INNER JOIN dbo_Sample ON dbo_Patient.PatientId = dbo_Sample.PatientId) INNER JOIN dbo_Test ON dbo_Sample.SampleID = dbo_Test.SampleId) INNER JOIN dbo_UserTest ON dbo_Test.TestId = dbo_UserTest.TestId
WHERE (((dbo_Patient.lastnm) Not In ("CDR","Research","UCLA","CAP","LABOTXBULK","SOFT-HLA","SOFTBULKIP6","SOFTBULKIP3","SOFT-ASAP-PEDS","TESTPatient")) AND ((dbo_Sample.LogTime) Between [Forms]![Form1]![Start Date] And [Forms]![Form1]![End Date]) AND ((dbo_Test.TestTypeCd) In ("AT1R","AT1R_RPT","C1Q_I","C1Q_I_RPT","C1Q_II","C1Q_II_RP","FL__PRAI","FL__PRAI_RPT","FL__PRAII","FL__PRAII_RPT","FL_EPC_XM_ALLO","FL_EPC_XM_ALLO_RPT","FL_XM_ALLO","FL_XM_ALLO_RPT","FL_XM_AUTO","FL_XM_AUTO_RPT","GP__I","GP__IDv2","GP__II","GP__MICA","GP_MICARPT","IBEAD_SABI","IBEAD_SABI_RPT","LCTI","LPRI","LPRI_RPT","LPRII","LPRII_RPT","LSM__MICA","LSMI","LSMI_RPT","LSMII","LSMII_RPT","LSMMICA_RPT","MICA_SAB","SABI","SABI_Dilution","SABI_IgM","SABI_RPT","SABII","SABII_Dilution","SABII_IgM","SABII_RPT","Serum_Stored")));
strSQL2 = "SELECT dbo_PatientReport.Patientid, dbo_PatientReport.ReportNm, dbo_PatientReport.SignedOffDt, dbo_PatientReportDetail.ParameterNm, dbo_PatientReportDetail.ValueTxt"
FROM dbo_PatientReport INNER JOIN dbo_PatientReportDetail ON dbo_PatientReport.PatientReportId = dbo_PatientReportDetail.PatientReportId
WHERE (((dbo_PatientReport.SignedOffDt) Between [Forms]![Form1]![Start Date] And [Forms]![Form1]![End Date]) AND ((dbo_PatientReportDetail.ParameterNm) Like "*Antibody*" Or (dbo_PatientReportDetail.ParameterNm) Like "*Crossmatch*") AND ((dbo_PatientReportDetail.ValueTxt)<>""));
'set up reference to the query to export
Set qdfTestData = CurrentDb.QueryDefs("TestData", strSQL)
Set qdfReportDate = CurrentDb.QueryDefs("ReporDate", strSQL2)
'Execute the query
Set rsTestData = qdfTestData.OpenRecordset()
Set rsReportDate = qdfReportDate.OpenRecordset()
'programatically refence Excel
Set XL = CreateObject("Excel.application")
'Set reference to the export workbook
Set wbTarget = XL.Workbooks.Open("W:\HLA.lab\Mila K\ACCESS\TEST.xlsm")
'clear excel sheet
wbTarget.Worksheets("TestData").Cells.ClearContents
'Use paste from recordset to put in excel sheet
wbTarget.Worksheets("TestData").Cells(1, 1).CopyFromRecordset rsTestData
'clear excel sheet
wbTarget.Worksheets("ReportDate").Cells.ClearContents
'Use paste from recordset to put in excel sheet
wbTarget.Worksheets("ReportDate").Cells(1, 1).CopyFromRecordset rsReportDate
'Add column heading names to both workbooks
For i = 1 To rsTestData.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
For i = 1 To rsReportDate.Fields.Count
xlApp.ActiveSheet.Cells(1, j).Value = MyRecordset.Fields(j - 1).Name
Next j
xlApp.Cells.EntireColumn.AutoFit
End With
'save workbook
wbTarget.Save
End Sub
Last edited:
