Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting access table to excel template

Status
Not open for further replies.

gibblin

Technical User
Joined
Oct 9, 2003
Messages
27
Location
GB
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!
 
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"

 
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
 
have you tried specifying a set range?

instead of using "Sheet1"

use "Sheet1!A1:C5"...
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top