Access 2010 Export Report to Excel VBA

[lowanam1]

I need assistance exporting a report to an excel template. I am not too familiar with vba code but I know that this is the best way to accomplish this. The name of the excel sheet is "csl" The location of the excel file is C:\users\lowana.hernandez
The export should begin in range A6
the name of the query that is pulling the data for the report is "qrycslw/selections"

Any help would be greatly appreciated.
Thanks

 

[pbaldy]

This should get you started:

http://support.microsoft.com/kb/247412

 

[lowanam1]

This is what I came up with. I may be way off I dont know..The red areas I dont know what to put. Thanks for the help

'Create a Recordset from all the records in the CSL report
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Users\lowana.hernandez\Desktop\SCG 7-22-13.accdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("rptcslw/selectionsbyworkitem", , adCmdReport)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A4").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\users\lowana.hernandez\csl"
oExcel.Quit

'Close the connection
rs.Close
conn.Close

 

[pbaldy]

Is the Access file the same one the code is in? If so, try this (I'm more comfortable with DAO recordsets):

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("[rptcslw/selectionsbyworkitem]", dbOpenDynaset)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A4").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\users\lowana.hernandez\csl"
oExcel.Quit

'Close the connection
Set rs = Nothing
Set db = Nothing

 

[lowanam1]

Thank you...is there something that I should do to make it run? because nothing happens. Thanks

 

[pbaldy]

You might test it behind a button on a form.