[AccessD] Export To Excel - Formulas Possible?

Here is a quick and dirty function to add the total formula to a column. It
assumes you know the starting cell of the numbers to be summed (E5 in this
ex). It puts the formula in the first empty cell of the column. Hopefully it
will get you started. I wrote this inside Excel. You'll need to set the
Excel object to run it from inside Access
Jim Hale

Function test()
Dim rng As Range, i As Integer, strAddressBottom As String, strAddressTop As
String
Set rng = Range("e5")
strAddressTop = rng.Address
For i = 1 To 25 'assumes 25 is the max length of the column of numbers to be
added
    If rng.Offset(i, 0).Value = "" Then 'looks for the first blank cell in
the column
        strAddressBottom = rng.Offset(i - 1, 0).Cells.Address
        rng.Offset(i, 0).Formula = "=sum(" & strAddressTop & ":" &
strAddressBottom & ")"
        GoTo The_End
    End If
Next
The_End:
	Set rng = Nothing
End Function



-----Original Message-----
From: Lawrence Mrazek [mailto:lmrazek at lcm-res.com]
Sent: Thursday, November 17, 2005 4:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Export To Excel - Formulas Possible?


Hi Jim:

I'll need to populate it by groups ... Each group will have 12 rows and the
same number of columns. So I'm thinking that I'll have start at a set
position for each sheet, and then populate down from there, skipping a
couple of rows after each grouping level. Basically, I'll need the company
total to be at the top, followed by all of the subsets of the data grouped
by category. 

Data will look something like this (will probably have about 9 columns
total):

Heading: Total Data By Company
Month:	PriorYrSales	CurrYrSales		%Increase (calculated)	
Jan		10			10			0%
Feb		33			66			100%
Mar		111			111			0%
Apr		112			112			0%
May		111			111			0%
Jun		444			444			0%
Total		---			---			

Heading: Total Data By Category 1
Month:	PriorYrSales	CurrYrSales		%Increase (calculated)	
Jan		10			10			0%
Feb		33			66			100%
Mar		111			111			0%
Apr		112			112			0%
May		111			111			0%
Jun		444			444			0%
Total		---			---

I'm thinking of looping through recordsets to get the separate data by
category.   


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.