exporting to excel template

[bigmac]

hi all, is it possible to create an excel template and use it when exporting a table using the external data, export to excel option? if so how?

 

[arnelgp]

you will need Automation to do that.
open the "formatted" workbook.
open the recordset from table
workbook.range("A1").copyfromrecordset theRecordset.

 

[Chief]

you will need Automation to do that.
open the "formatted" workbook.
open the recordset from table
workbook.range("A1").copyfromrecordset theRecordset.

G'Day arnelgp,

Am I able to ask you to elaborate on this for me please?
This is my current code that just exports straight out into excel. (On vbYes)
But I want to advance on this to open in an excel template where I will have formulas to calculate imported data. (If Possible)

Private Sub BtnOps_SchedAss_Click()
Dim iExport As String
iExport = ""
    iExport = MsgBox("Do you want to Export to Excel?" & vbCrLf & "Export Options ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Export")
   
    Select Case iExport
        Case vbYes: ' Export Data to Excel File
            DoCmd.OutputTo acOutputQuery, "Ops_AssSchedFCastQ", acFormatXLSX, , True
        Case vbNo: ' View as Datasheet
            DoCmd.OpenQuery "Ops_AssSchedFCastQ", acViewNormal
    End Select
End Sub

Excel:

I will be putting more formulas in but this is an example.
Hopefully you can help.
Thanks Mate

EDIT: Would it be better to export the date into a workbook, into its own work sheet, then have another worksheet already drawing from the data? hmmm

 

[Isaac]

I prefer CopyFromRecordset, as the built-in Access commands (OutputTo, TransferSpreadsheet), have caused me trouble either in Access front ends with tables and nav pane hidden as well as cutting off long text. CopyFromRecordset seems to have the least overall problems

 

[Chief]

you will need Automation to do that.
open the "formatted" workbook.
open the recordset from table
workbook.range("A1").copyfromrecordset theRecordset.

Here is a sample DB
I have also included the excel template workbook.

I think It would be good to bring in the data on worksheet Ops_AssSchedFCastQ, deleting old data and replacing with new.
Then The Results sheet stays in place and calculates off the Data sheet.

What do you think?