[AccessD] Excel Automation ... Setting Default Formatting InE xcel

I use this code open the template:
strPathname="c:\template.xlt"
appExcel.Workbooks.Open strPathname, 0
The path I actually use is the path where Excel stores the templates by
default, something like
C:\Documents and Settings\jhale\Application
Data\Microsoft\Templates\TrialBal_Excel_6mo.xlt

In the code segment below I copy the worksheet Dept1 as many times as called
for from a recordset:
 'routine to create and rename Worksheets
    recset(1).MoveFirst
    Dim y As Integer, z As Integer, x As Integer
    z = .Worksheets("Dept1").Index
   
      For y = z To intRst4cnt + z - 1
      strName = recset(1)("fldDPname")
        .Worksheets(y).Name = strName
        .Worksheets(y).Select
        .Range("A10").FormulaR1C1 = y - z + 1 'set index number of sheet
        
        .Range("A1").Select
         If Not recset(1).RecordCount = y - z + 1 Then
          .Worksheets(z).Copy After:=Worksheets(y)
         End If
        If Not recset(5).EOF Then recset(1).MoveNext
    Next y
I am sending you a sample database off list that shows how to paste data
into template. HTH
Jim Hale

-----Original Message-----
From: Lawrence Mrazek [mailto:lmrazek at lcm-res.com]
Sent: Friday, March 31, 2006 1:07 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Excel Automation ... Setting Default Formatting
InExcel


Thanks Jim:

I'm currently using the following code to create my workbook:
Set xlApp = New Excel.Application
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Add

To use a template, do I simply alter the last line so that it points to a
template?

    Set xlWb = xlApp.Workbooks.Add("c:\template.xlt")

Also, how do you make sure that each new worksheet inherits the template's
formatting? I'm using the following code to add the worksheet:

Set xlWs = xlWb.Worksheets.Add

Thanks in advance for all of your advice.

Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314.432.5886
fx. 314.432.3304



***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.