save excel template to another excel workbook from within Access

[Guest]

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.

 

[Guest]

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'