Exporting access table to excel template

[gibblin]

I have a table in access that's been manipulated & created specifically for exporting to an excel document. The reason it needs to be in an excel format is for flexibility.
I've got a template that i want to export it to, therefore the first row in the spreadsheet doesn't contain the row headers, and there are calculated fields/boxes at the bottom of the spreadsheet.
I've figured out how to open the template & save it as a new .xls file using VB, but when i export the table using the transferspreadsheet command it merely adds it to a new sheet within the workbook, instead of being intergrated into the template sheet.
Please help!

 

[gibblin]

For information my code is as follows:-

Dim ExcelTemplate As Object

Set ExcelTemplate = CreateObject("Excel.Application")

With ExcelTemplate
.Visible = True
.Workbooks.Add ("C:\Template.xlt")
.ActiveSheet.UsedRange.Columns.AutoFit
.Activeworkbook.SaveAs filename:="C:\Report" & Format(Date, "mm-dd") & ".xls"
.Activeworkbook.Close
.Workbooks.Open ("C:\Report" & Format(Date, "mm-dd") & ".xls")
End With

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Report" & Format(Date, "mm-dd") & ".xls", True, "Sheet1"

 

[PHV]

Have you tried to pull the data in excel instead of push them in access ?
I you do so, take a look at the properties of the QueryTable like FillAdjacentFormulas, FieldNames, ...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

have you tried specifying a set range?

instead of using "Sheet1"

use "Sheet1!A1:C5"...

 

[gibblin]

Apologies for the delay in replying and thank you both for your replies. However, i've decided to give the user the option of editing the 'report' on a form and then exporting the report in word format, keeping the flexibility of excel (to the level they require) and having the presentational (??) aspects of the template.

Cheers.