[AccessD] Excel Automation ... Setting Default FormattingInE xcel

Thanks Jim for all of the help ... I didn't even need to modify my code that
much!

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


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Friday, March 31, 2006 2:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Excel Automation ... Setting Default FormattingInE
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.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com