Opening/editing/saving excel file

[cherman]

I am using Access/Excel 2003 and I'm trying to open a file called
Quad_Charts_Template.xls from my C drive, dump some data on one of the
sheets, make a couple changes and save it with a specific name.

This is the code I have to choose my record sorce:
Set rst = CurrentDb.OpenRecordset("qry_Avg_Age_Trend_Source_Data")

The worksheet is called "AAT_Raw_Data", which will always be the 1st sheet
and I want to copy the data from rst onto the sheet starting at A1.

I also want to bold the header row and set the auto filter as active.

And finally I want to save the file as "abc" in the same folder.

I have some code working and some that doesn't so I thought it would be good
to get the complete code from someone.

Any help would be greatly appreciated!

 

[Ken Snell]

See if this article is helpful for getting you started:

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#WriteRstFile

 

[ryguy7272]

Ken's stuff is great! I've used it too many times to count!! Also, check
out this code for using Excel from Access:

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database

Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile As String

strFile = "C:\Documents and Settings\Desktop\Ryan\MyWorkbook.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible

Set objExcel = New Excel.Application

objExcel.Visible = True

' Opens up a Workbook

Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile

xlWB.Close

xlapp.Quit

Set xlapp = Nothing

End Sub

HTH,
Ryan---