HELP!! - Exporting Access tables to Excel

  • Thread starter Thread starter paul donnelly
  • Start date Start date
P

paul donnelly

I am currently creating a reporting application in Access
2002 and need to export multiple tables to different MS
Excel files (Excel 2002).

These excel files will be premade templates. What I need
Access to do is automatically (or click of a button) to
send these tables into the premade excel templates on a
weekly basis.

a) Is there a simple way to export these tables
automatically to excel.
b) the data has to go into certain worksheets in the
excel templates.
c) some of the text has to appear in different font color
in excel.
d) this has to be as automated as possible

any help would be greatly appreciated...
 
Here's a hint ...


Dim x As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook


'Copy the template
strDestFile = cstrFileDest & strRepMonth & ".xls"
FileCopy cstrTemplate, strDestFile


Set x = New Excel.Application
Set wb = x.Workbooks.Open(strDestFile)

Set ws = wb.Worksheets("Jan01")
ws.Select
ws.Cells(1, 1) = "date"
ws.Cells(1, 2) = "text1"
ws.Cells(1, 3) = "Text2"
ws.Cells(1, 4) = "Text3"
 
Hi Paul,

To have full control of whereabouts in the Excel templates the data is
put and just how it is formatted, you'll need to write VBA code using
Automation and the Excel object model. The following links should help,
and you'll probably need Excel's Range.CopyFromRecordset method.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235
 
Back
Top