Exporting Access Query Data to Specific Cells in Excel: Guidance Needed

Thank you, I will look into it. By specific cells --- very simple. I would like to export 3 pieces of data from the query to cells B7, B8 and B8. Hopefully, that is clear.

You'll find an example of exporting data from Access to Excel in a demo database on my website.

It shows one method. There are other approaches that might work as well.

Perhaps, though, it would be easier to provide more directed suggestions if you could provide a more detailed explanation of what you mean by "specific cells in Excel."

The code sample I have handy is probably too complex for your current needs, although does designate a range of cells for the output.

Public Sub ExportNPIntakeReports(dtStartDate As Date, dtEndDate As Date)

Dim qdef As DAO.QueryDef

Dim xlapp As Object

Dim strtemplate As String

Dim strreportPath As String

Dim strSQL As String

Dim strOutPutReport As String

Dim strRange As String

Dim LastRowIntakeDate As Long

Dim LastRowIntakeVal As Long

Dim LastRowNPDate As Long

Dim LastRowNPVal As Long

Dim LastRowAVKAPctVal As Long

Dim LastRowAVKAPctDate As Long

Dim LastRowSummary As Long

Dim LastRowPVDDate As Long

Dim LastRowPVDVal As Long

Dim i As Long

Dim db As Database

Dim rst As DAO.Recordset

Dim rstclinics As DAO.Recordset

On Error GoTo ErrHandler

Set db = CurrentDb

Select Case fRolecheck(strUserID:=fOSUserName)

Case 1

strtemplate = "IntakeNPReports.xltm"

Case Else

strtemplate = ""

MsgBox Prompt:="Report run by Executive Managers Only", Buttons:=vbOKOnly, Title:="Not Authorized"

GoTo exitProc

End Select

If DCount("Clinic", "qryClinicNPINTakesBySelectedDateRange") = 0 Then

MsgBox Prompt:="There are no records in the selected Date Range", Buttons:=vbOKOnly, Title:="Select a Different Date Range"

GoTo exitProc

End If

strreportPath = CurrentProject.Path

Set xlapp = CreateObject("Excel.Application")

With xlapp

.Workbooks.Open strreportPath & "\" & strtemplate

.Application.DisplayAlerts = False

'' .Visible = True

strSQL = "SELECT TreatmentTypeCode, A, B, C, D, HQ FROM qryClinicNPINTakesBySelectedDateRange_IntakesXtab"

'2012-12-19 Export the NP section

'check for records in the selected date range

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If Not rst.EOF And Not rst.BOF Then

.worksheets("Intake_NP").Select

.range("IntakeSection").Select ' Send it to the detail section

.activecell.Offset(0, 0).Value = "Intakes"

For i = 1 To rst.Fields.Count - 1

.activecell.Offset(0, i).Value = rst.Fields(i).Name

Next i

.range("IntakeSectionDetail").CopyFromRecordset rst

End If

strSQL = "SELECT TreatmentTypeCode, A, B, C, D, HQ FROM qryClinicNPINTakesBySelectedDateRange_NewPatientXtab"

'2012-12-19 Export the NP section

'check for records in the selected date range

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If Not rst.EOF And Not rst.BOF Then

.worksheets("Intake_NP").Select

.range("NPSection").Select ' Send it to the detail section

.activecell.Offset(0, 0).Value = "NP"

For i = 1 To rst.Fields.Count - 1

.activecell.Offset(0, i).Value = rst.Fields(i).Name

Next i

.range("NPSectionDetail").CopyFromRecordset rst

End If

strSQL = "From " & dtStartDate & " Through " & dtEndDate

.worksheets("Intake_NP").Select

.range("DateRange").Select ' Send it to the detail section

.activecell.Offset(0, 0).Value = strSQL

.range("A1:A1").Select

strOutPutReport = strreportPath & "\NP_Intakes" & Format(Now(), "yyyy_mm_dd_HHmmss") & ".xlsx"

.ActiveWorkbook.SaveAs FileName:= _

strOutPutReport, FileFormat:= _

51, CreateBackup:=False

xlapp.Quit

ShellExecute hwnd:=0, lpOperation:="Open", lpFile:=strOutPutReport, lpParameters:=0, lpDirectory:=0, nShowCmd:=1

End With

Cleanup:

On Error Resume Next

exitProc:

On Error GoTo 0

Exit Sub

ErrHandler:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportNPIntakeReports of VBA Document Form_frmDashboardNoCharts"

Resume Cleanup:

Resume

End Sub

The relevant parts for your needs are where a range is designated and selected, followed by the

CopyFromRecordset method to export the query to that range.

You are correct. That is "way too complex" for probably my needs and, more important, to my understanding. I assume there is nothing where I "can paint by the numbers".

Thank you for your help.

Thanks for the response. I agree you would need more information to provide a correct solution. Since the post and since what I am trying to accomplish is simple, I found a simple cut and paste is the easiest approach and works well.

Again, thanks for your help.