copy Excel Template and Open to view

[LeftyLeo via AccessMonster.com]

Hi all,
I am not sure how to do the following in vba code: I want to copy the blank
excel template and name it, then fill the copy and finally to have the excel
spreadsheet open to view for the user. Here is the code I have that works
great to fill the template, but then I need to clear it and I am trying to
avoid this.

Private Sub cmdExport_Click()
Dim rec As DAO.Recordset
Dim objApp As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim objRange As Excel.Range
Dim retVal As String


DoCmd.Hourglass True

'Open Excel workbook
Set objApp = New Excel.Application
Set objWorkbook = objApp.Workbooks.Open("C:\Documents and Settings\at74328\My
Documents\Promo1285\Enterprise Standard Entry Uploadnew.xls")
Set objSheet = objWorkbook.Sheets("Standard Entry")


'Export variables to Excel spreadsheet
objWorkbook.Sheets("Standard Entry").Range("A3").Value = "701"
objWorkbook.Sheets("Standard Entry").Range("B3").Value = sisCurYearFiscal
(Date)
objWorkbook.Sheets("Standard Entry").Range("C3").Value = Right(ClosingPd(Date)
, 2)
objWorkbook.Sheets("Standard Entry").Range("D3").Value = Right(ClosingPd(Date)
, 2) * 4

Set rec = CurrentDb.OpenRecordset("qryEJExport")
objSheet.Range("A8").CopyFromRecordset rec


'Close Workbook
objApp.Workbooks("Enterprise Standard Entry UploadNew.xls").Close SaveChanges:
=True
'Close Excel
objApp.Quit
Set objApp = Nothing
MsgBox "Export complete"
DoCmd.Hourglass False

End Sub

Can anyone help with this? I already searched the forum for an answer that
how I got this far. I love this site.

Thanks in advance,
Anna

 

[LeftyLeo via AccessMonster.com]

I thought I didn't need to copy because it overwrites but only in the lines
that are needed any extra remains and makes the information wrong. So I still
need to copy the template and fill the copy.

 

[Roger Carlson]

If you actually save your "template" as a Template (that has a .xlt
extension), it will create a brand new spreadsheet automatically.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[LeftyLeo via AccessMonster.com]

If you actually save your "template" as a Template (that has a .xlt
extension), it will create a brand new spreadsheet automatically.

Hi all,
I am not sure how to do the following in vba code: I want to copy the

[quoted text clipped - 54 lines]

Thanks in advance,
Anna

Okay I will try that! Thanks

 

[LeftyLeo via AccessMonster.com]

If you actually save your "template" as a Template (that has a .xlt
extension), it will create a brand new spreadsheet automatically.

[quoted text clipped - 4 lines]Okay I will try that! Thanks

I couldn't get it to work, kept getting a script 9 out of range error?

 

[Roger Carlson]

It's very hard to tell what that would cause that error given the
information we have. However, on my website (www.rogersaccesslibrary.com),
is a small Access database sample called "ExportToExcel.mdb" which does
something like what you are trying to do. Perhaps if you downloaded that
and compare what I'm doing to your code, you'll come up with something.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

If you actually save your "template" as a Template (that has a .xlt
extension), it will create a brand new spreadsheet automatically.

[quoted text clipped - 4 lines]

Thanks in advance,
Anna

Okay I will try that! Thanks

I couldn't get it to work, kept getting a script 9 out of range error?

 

[LeftyLeo via AccessMonster.com]

It's very hard to tell what that would cause that error given the
information we have. However, on my website (www.rogersaccesslibrary.com),
is a small Access database sample called "ExportToExcel.mdb" which does
something like what you are trying to do. Perhaps if you downloaded that
and compare what I'm doing to your code, you'll come up with something.

If you actually save your "template" as a Template (that has a .xlt
extension), it will create a brand new spreadsheet automatically. [quoted text clipped - 3 lines]
Okay I will try that! Thanks

I couldn't get it to work, kept getting a script 9 out of range error?

I will do that! Thanks so much for your assistance.

 

[LeftyLeo via AccessMonster.com]

It's very hard to tell what that would cause that error given the
information we have. However, on my website (www.rogersaccesslibrary.com),

[quoted text clipped - 7 lines]
I will do that! Thanks so much for your assistance. Your database helped so much. I got it to create a copy and then used a macro Runapp to start the spreadsheet up. Thanks again for your help and great ideas.