[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