[AccessD] Sum a column in Excel files in a specific folder

Ervin Brindza viner at EUnet.yu
Fri Mar 16 02:55:15 CDT 2007


Hi,
Jim & Jim thanks for the suggestion and for code. It'll be good start to 
achieve my goal: not to open each file manually and insert some code, but to 
have code that will  sum every column E in every .xls file in a specific 
folder!
Many thanks again!
Ervin

----- Original Message ----- 
From: "Hale, Jim" <Jim.Hale at FleetPride.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, March 15, 2007 3:38 PM
Subject: Re: [AccessD] Sum a column in Excel files in a specific folder


> Here is one quick and dirty way. It searches the first 1000 rows until it
> finds the first empty row in the column, assumes that is the end of the
> range to be summed, and places a sum formula in that cell. HTH
> Jim Hale
>
> Function test()
> Dim rng As Range, i As Integer, strAddressBottom As String, strAddressTop 
> As
> String
> Set rng = Range("e1")
> strAddressTop = rng.Address
> For i = 1 To 1000
>    If rng.Offset(i, 0).Value = "" Then
>        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: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ervin Brindza
> Sent: Thursday, March 15, 2007 3:21 AM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Sum a column in Excel files in a specific folder
>
> Hi,
> I have a couple of xls files in some folder and need to sum exactly the
> column E in every of them. The number of rows isn't the same in these 
> files.
> Can somebody help me?
> TIA,
> Ervin
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> ***********************************************************************
> 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
> 




More information about the AccessD mailing list