Export data in a Excel-template? 1

[gerard1979]

I'm using Access2000
is it possible with transferspreadsheet to export to a template?
I make a table in Access from a couple of queries and want to export this to excel to copy the information to other applications, but the currency fields are falling down to numeric fields. So I try to make a template, but then the function takes the next worksheet to export./

Is it possible to handle this in other ways?

Gerard

 

[AWithers]

In the past I have got round this by first of all exporting to a spreadsheet then working with the Excel com object copy the data from that spreadhseet to a suitable template (using paste special, values#) to maintain the formattinbg in the template

e.g.

'first transfer data to Excel
DoCmd.OutputTo acOutputQuery, monthofreport + "report", acFormatXLS, "q:\revenueanalysis\temp.xls", False

'set up variables for working with Excel

Dim objExcel As New Excel.Application ' Excel application object
Dim objExcelDoc As Object ' Excel object - workbook

' create Excel
Set objExcel = New Excel.Application

'open relevant document template

Set objExcelDoc = objExcel.Workbooks.Add("q:\revenueanalysis\reporttemplate.xlt&quot 'template file
Set objExcelDoc = objExcel.Workbooks.Add("q:\revenueanalysis\temp.xls&quot 'the data file

Set objExcelDoc = Nothing 'clear memory
objExcel.Visible = True 'show excel

'now working in Excel

'first transfer data to 'master' sheet in template file
With objExcel
.Windows("temp1&quot.Activate
.Cells.Select
.Selection.Copy
.Windows("ReportTemplate1&quot.Activate
.Sheets("Master&quot.Select
.Range("A1&quot.Select
.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


etc

etc...

Hope this helps

Andy

 

[ETID]

You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properies to refresh on open...fill formulas etc.

 

[gerard1979]

It works great!
Tnx