Access query output to MS Excel template

[Guest]

Hi, I am trying to send MS access query output to a predefined MS excel
template and create an excel file for each record in my query. Does anyone
know how I can do this? Thanks

 

[arthurjr07]

I am trying to send MS access query output to a predefined MS excel

template

You can do it in MS excel template. Open that excel,
on the Menu
Select Data>Import External Data> New Database Query.
Selct <New Data Source> and follow the procedure.

or you can also use vba.

 

[arthurjr07]

create an excel file for each record in my query
you can only do this in vba,

Here some for your reference

Sub Transfer()
Dim db As DAO.Database
Dim wc_rc As Recordset


Dim ExcelApp As Object
Dim wrkbuk As Object




Set db = CurrentDb

Set wc_rc = db.OpenRecordset("QueryName")
wc_rc.MoveFirst
Do While Not wc_rc.EOF

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
Set wrkbuk = ExcelApp.Workbooks.Add

ExcelApp.Cells(1, 2).Value = wc_rc!FieldName1
ExcelApp.Cells(2, 2).Value = wc_rc!FieldName2


wrkbuk.SaveAs "C:\" & CStr(wc_rc!OrderNo) & ".xls"
wrkbuk.Close
ExcelApp.Visible = False
Set wrkbuk = Nothing
Set ExcelApp = Nothing
wc_rc.MoveNext

Loop
End sub

Goto your Access files
create new module then copy and paste the code
above.Be sure to supply all necessary items
{QueryName, FieldName1, FieldName2}

hope this will help