Access query output to MS Excel template

S

Simon

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
 
A

arthurjr07

I am trying to send MS access query output to a predefined MS excel
template
Click to expand...

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.
 
A

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
 
Top