Excel export from template

[RobinAG]

Hello,

I'm trying to create a new Excel document from a template I've created (Export.xls). I'm having trouble getting it to work, sometimes it works, sometimes it gives me a run-time error '70', permission denied. Here's the code I'm using:

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim CurrentDate As String
Dim sWorkBook As String

DoCmd.Hourglass True

CurrentDate = Format(Date, "mm.dd.yyyy")
sWorkBook = "C:\Test\Export " & CurrentDate & ".xls"

Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wbk.SaveAs (sWorkBook)
FileCopy "C:\Test\Export.xls", sWorkBook


Any ideas?

[RobinAG]

I got it to work, I needed to get rid of the wbk.SaveAs statement. I don't know, however, why I was getting the permission denied error.

Hello,

I'm trying to create a new Excel document from a template I've created (Export.xls). I'm having trouble getting it to work, sometimes it works, sometimes it gives me a run-time error '70', permission denied. Here's the code I'm using:

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim CurrentDate As String
Dim sWorkBook As String

DoCmd.Hourglass True

CurrentDate = Format(Date, "mm.dd.yyyy")
sWorkBook = "C:\Test\Export " & CurrentDate & ".xls"

Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Add
Set wks = appExcel.Worksheets(1)
wbk.SaveAs (sWorkBook)
FileCopy "C:\Test\Export.xls", sWorkBook


Any ideas?