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

Hi Ervin:

Just use: Application.Quit

...and that will close the sheet.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ervin Brindza
Sent: Friday, March 16, 2007 3:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sum a column in Excel files in a specific folder

A.D., Gustav,
many thanks for the suggestions! AD's solution is perfect for one xls file 
with multiple sheets.
But I solved my problem by Jim Hale's (quick and dirty ;-) suggestion, 
because there is more xls(actually .dbf) files in a folder, with only one 
sheet, generated by some external system.
If someone needs my code let me drop a line, although I have some problem 
with closing the Excel.
The procedure leaves open the Excel ;0(((
Sub Excel_CloseWorkBook(xlApp As Excel.Application, Optional bSaveChanges As

Boolean = False)
Dim wb As Excel.Workbook
    On Error Resume Next
    If xlApp.Name > "" Then
    End If
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
        For Each wb In xlApp.Workbooks         'Close all open workbooks
            wb.Close bSaveChanges
        Next wb
    xlApp.UserControl = False
    Set xlApp = Nothing
End Sub

Many thanks again for the suggestions!
Ervin
PS AD is it worth for new sample to Roger's site?

----- Original Message ----- 
From: "A.D.TEJPAL" <adtp at hotmail.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Friday, March 16, 2007 10:25 AM
Subject: Re: [AccessD] Sum a column in Excel files in a specific folder


> Ervin,
>
>    Sample function named  Fn_GetColSumAllSheets(), as given below, will 
> get you the total sum of given column on all sheets combined, in specified

> excel workbook. Column number & full path of excel file are passed as 
> arguments to this function.
>
>    The function is to be placed in a suitable module in access db. 
> Reference to MS Excel Object Library (appropriate version) is required.
> Sum of target column in individual worksheets is also reflected in the 
> immediate window, via Debug.Print statements.
>
>    Typical statement for getting the overall sum in column E for all 
> worksheets combined, into a variable named TotSum would be:
>
>    TotSum = Fn_GetColSumAllSheets(5, ExcelFilePath)
>
> Best wishes,
> A.D.Tejpal
> ---------------
>
> ==================================
> Function Fn_GetColSumAllSheets( _
>                ByVal ColNum As Long, _
>                ByVal ExcelFilePath As _
>                String) As Double
>    ' Gets overall sum of column ColNum
>    ' in all sheets combined in workbook
>    ' having ExcelFilePath as its full path
>    Dim exp As Excel.Application
>    Dim ws As Excel.Worksheet
>    Dim rg As Excel.Range
>
>    Dim LastFilledRow As Long
>    Dim SheetSum As Double
>    Dim TotSum As Double
>
>    Set exp = New Excel.Application
>    exp.Workbooks.Open ExcelFilePath
>
>    TotSum = 0
>    For Each ws In exp.ActiveWorkbook.Sheets
>        LastFilledRow = ws.Cells(Rows.Count, _
>                                ColNum).End(xlUp).Row
>        Set rg = exp.Range(ws.Cells(1, ColNum), _
>                    ws.Cells(LastFilledRow, ColNum))
>        SheetSum = exp.WorksheetFunction.Sum(rg)
>        TotSum = TotSum + SheetSum
>
>        Debug.Print "Column " & ColNum & _
>                    " - Sum For Sheet " & ws.Name & _
>                    " = " & SheetSum
>    Next
>
>    Fn_GetColSumAllSheets = TotSum
>
>    Debug.Print "Column " & ColNum & _
>                    " - Sum For All Sheets " & _
>                    " = " & TotSum
>
>    Set rg = Nothing
>    Set ws = Nothing
>    exp.Quit
>    Set exp = Nothing
> End Function
> ==================================
>
>  ----- Original Message ----- 
>  From: Ervin Brindza
>  To: AccessD at databaseadvisors.com
>  Sent: Thursday, March 15, 2007 13:51
>  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
>
>
> -- 
> Internal Virus Database is out-of-date.
> Checked by AVG Free Edition.
> Version: 7.5.441 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 
> 9:58 PM
>
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com