Sum range from different files of one folder

[Knigge]

Hi Everyone!

Every week I have to write a cost overview in one standard Excel 2003 Sheet in one file. That is since more than 18 months. All Text cells are on the same place. The numbers cells are in the range C10:I31. The rest is text. All the Excel-Files are in one Folder (e.g. c:\temp)

Now, I need have an overview of those costs and to sum them in one file.

Any help is welcome. :wink:

best regards

Knigge

 

[Fazza]

Welcome, Knigge. How about this? Please modify to suit. Fazza

Sub test()

    Const sFilePath As String * 8 = "C:\temp\"
    Const sSheetName As String = "your sheet name"
    Const sRangeAddress As String * 7 = "C10:I31"
    
    Dim dSum As Double
    Dim sFileName As String
    Dim sPart1 As String
    Dim sPart2 As String
    
    sPart1 = Join$(Array("SUM('", sFilePath, "["), vbNullString)
    sPart2 = Join$(Array("]", sSheetName, "'!", Range(sRangeAddress).Address(ReferenceStyle:=xlR1C1), ")"), vbNullString)
    
    sFileName = Dir(sFilePath)
        
    Do While Len(sFileName)
        dSum = dSum + ExecuteExcel4Macro(Join$(Array(sPart1, sFileName, sPart2), vbNullString))
        sFileName = Dir()
    Loop
    
    MsgBox dSum

End Sub

 

[Knigge]

Thank you for the welcome and the fast answer Fazza!

I've tried your code and it seems to work . The only point: how should I proceed to get the same format (C10:I31) back? (to get the sum of all C10's, C11's... until I31 instead of one finale Value? )

Best regards... and looking forward!


Knigge

P.S.: the final value should be in the sheet of the file

 

[Fazza]

OK. With a message in the status bar to show progress. cheers, Fazza

Sub test()

    Const sFilePath As String * 8 = "c:\temp\"
    Const sSheetName As String = "sheet name on source files"
    Const sRangeAddress As String * 7 = "C10:I31"
    
    Dim cel As Range
    Dim dSum As Double
    Dim lCellsDone As Long
    Dim lTotalCellsToDo As Long
    Dim rDataBlock As Range
    Dim sFileName As String
    Dim sPart1 As String
    Dim sPart2 As String
    
    Application.ScreenUpdating = False
    
    Set rDataBlock = Range(sRangeAddress)
    lCellsDone = 0
    lTotalCellsToDo = rDataBlock.Cells.Count
    
    sPart1 = Join$(Array("SUM('", sFilePath, "["), vbNullString)
    
    For Each cel In rDataBlock
        dSum = 0
        sPart2 = Join$(Array("]", sSheetName, "'!", cel.Address(ReferenceStyle:=xlR1C1), ")"), vbNullString)
        sFileName = Dir(sFilePath)
            
        Do While Len(sFileName)
            dSum = dSum + ExecuteExcel4Macro(Join$(Array(sPart1, sFileName, sPart2), vbNullString))
            sFileName = Dir()
        Loop
        lCellsDone = lCellsDone + 1
        If lCellsDone Mod 10 = 0 Then Application.StatusBar = "Done " & lCellsDone & " out of " & lTotalCellsToDo
        cel.Value = dSum
    Next cel
    Application.StatusBar = False
    MsgBox "Done"
End Sub

 

[Knigge]

P E R F E C T!!! Great! Genious!!

Many many thanks! :wink: ray:

:-> Fazza is a God... :wink:

All the best from Switzerland!

from happy Knigge

 

[Fazza]

Knigge, This is routine stuff when you know it. The smart step is the use of the Excel4 macro function to obtain values from the closed workbooks. This existed long before I started coding & I know it from the work of John Walkenbach - I recommend his books and website should you wish to learn VBA. Glad it worked for you. Regards, Fazza