save excel template to another excel workbook from within Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kellie - 07 Jan 2006 00:16 GMT

I have an excel template that I transfer data to from an access query. Once I
have done this I would like from within Access to save the excel template as
a new excel workbook (new path and filename each time it is run). The
template has 3 worksheets to be saved/copied to the new workbook.

I'm not sure how to do this.

Signature

Greenie

Reply to this Message

Kellie - 07 Jan 2006 01:16 GMT

This is the code I have so far but I am getting run-time error 91

Function CopyExcel()

Dim ExcelApp As Excel.Application

Dim SourceName As String
Dim DestinationName As String
Dim Sworksheet1 As String
Dim Sworksheet2 As String
Dim Sworksheet3 As String
Dim MyRange As Excel.Range

SourceName = DLookup("[Reconciliation Template Location]", "tblPayPeriod")
DestinationName = DLookup("[Path]", "qryExportName")
Sworksheet1 = "Pay recon"
Sworksheet2 = "Recon_Data"
Sworksheet3 = "Header"
MyRange = "A1:F66"

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Workbooks.Open (SourceName)

ExcelApp.Workbooks(SourceName).Worksheets(Sworksheet1).Range(MyRange).SaveAs
ExcelApp.Workbooks(DestinationName).Worksheets(Sworksheet1).Range(MyRange)

ExcelApp.Quit

End Function

I am getting this error message

'Object variable or With block variable not set'

> I have an excel template that I transfer data to from an access query. Once I
> have done this I would like from within Access to save the excel template as
> a new excel workbook (new path and filename each time it is run). The
> template has 3 worksheets to be saved/copied to the new workbook.
>
> I'm not sure how to do this.

Reply to this Message