Opening/editing/saving excel file

  • Thread starter Thread starter cherman
  • Start date Start date
C

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'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---
 
Back
Top