Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Sum cells from multiple files in same folder

  1. #1
    New Member
    Join Date
    May 2006
    Location
    South Carolina
    Posts
    24

    Default Sum cells from multiple files in same folder

    I have multiple excel files (file number varies but now it's approx 30) in one folder location. All of the files are formatted exactly the same. Only the file names and cell contents differ.
    Is there is a way to sum (or get) the values of the same cell and/or range in all of the files into a new excel file worksheet?

    Example: Sum or get value in cell "A1" from Sheet1 of all files in C:\excelfolder.
    and
    Sum or get values in range "A1:B2" from Sheet1 from all files in C:\excelfolder.

    Thanks for any assistance.

  2. #2
    Board Regular Warship's Avatar
    Join Date
    Jul 2007
    Location
    Florida
    Posts
    1,020

    Default Re: Sum cells from multiple files in same folder

    Regards, Warship
    Excel 2007 | 2010 on XP | Vista | 7 | 8.1

    We learn by teaching... Please complete the learning curve with feedback.

    Get Lost... Support Search & Rescue.

    Frustration is due to lack of knowledge. Lack of knowledge is due to frustration. Relax.

    Quandary: How is it that the Calendar is moving so fast when the Clock is moving so slow?

    VBA BASICS: http://www.contextures.com/xlvba01.html

    ALWAYS USE A COPY OF YOUR FILE WHEN TESTING CODE !

  3. #3
    New Member
    Join Date
    May 2006
    Location
    South Carolina
    Posts
    24

    Default Re: Sum cells from multiple files in same folder

    HTH,
    Thanks for the quick reply. I'm looking at it now. I apologize for my ignorance. Which or all of the examples should I use? Copy and paste in sheet, workbook, form, or module? Thanks again!

  4. #4
    Board Regular Warship's Avatar
    Join Date
    Jul 2007
    Location
    Florida
    Posts
    1,020

    Default Re: Sum cells from multiple files in same folder

    Let me wrap my head around your specifics and post back.

    (P.S. "HTH" = "Hope this helps")
    Regards, Warship
    Excel 2007 | 2010 on XP | Vista | 7 | 8.1

    We learn by teaching... Please complete the learning curve with feedback.

    Get Lost... Support Search & Rescue.

    Frustration is due to lack of knowledge. Lack of knowledge is due to frustration. Relax.

    Quandary: How is it that the Calendar is moving so fast when the Clock is moving so slow?

    VBA BASICS: http://www.contextures.com/xlvba01.html

    ALWAYS USE A COPY OF YOUR FILE WHEN TESTING CODE !

  5. #5
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Sum cells from multiple files in same folder

    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    May 2006
    Location
    South Carolina
    Posts
    24

    Default Re: Sum cells from multiple files in same folder

    Warship & VoG
    Very steep learning curve on my part...but surprisingly fun none-the-less. I very much appreciate any and all suggestions and will attempt to explain the resolution (should I figure it out).

    Let me clarify a little as far as what I would "like to have" with the get function...

    Get value in cell "A1" from Sheet1 of .xls file in C:\excelfolder and place in cell "A1" of active sheet.

    Get value in cell "A1" from Sheet1 from next .xls file in C:\excelfolder and place in "A2" of active sheet.

    Continue so on and so on until the value of "A1" has have been retrived from all .xls files in C:\excelfolder and placed in active sheet's "A" coulmn...A1, A2, A3, A4...

  7. #7
    Board Regular Warship's Avatar
    Join Date
    Jul 2007
    Location
    Florida
    Posts
    1,020

    Default Re: Sum cells from multiple files in same folder

    Place in Std Module and Call LoopThruBooks
    Code:
    Private Function GetValue(path, file, sheet, ref)
        Dim arg As String
        If Right(path, 1) <> "\" Then path = path & "\"
        arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(ref).Range("A1").Address(, , xlR1C1)
        GetValue = ExecuteExcel4Macro(arg)
    End Function
    
    Sub LoopThruBooks()
        Dim p, f, s, a, r
        p = "C:\excelfolder\"
        f = Dir(p & "*.xls")
        Do While f <> ""
            s = "Sheet1"
            a = "A1"
            r = r + 1
            Range("A" & r) = GetValue(p, f, s, a)
            f = Dir()
        Loop
    End Sub
    Regards, Warship
    Excel 2007 | 2010 on XP | Vista | 7 | 8.1

    We learn by teaching... Please complete the learning curve with feedback.

    Get Lost... Support Search & Rescue.

    Frustration is due to lack of knowledge. Lack of knowledge is due to frustration. Relax.

    Quandary: How is it that the Calendar is moving so fast when the Clock is moving so slow?

    VBA BASICS: http://www.contextures.com/xlvba01.html

    ALWAYS USE A COPY OF YOUR FILE WHEN TESTING CODE !

  8. #8
    Board Regular Warship's Avatar
    Join Date
    Jul 2007
    Location
    Florida
    Posts
    1,020

    Default Re: Sum cells from multiple files in same folder

    "s" & "a" didn't need to be in the loop.
    Code:
    Sub LoopThruBooks()
        Dim p, f, s, a, r
        p = "C:\excelfolder\"
        f = Dir(p & "*.xls")
        s = "Sheet1"
        a = "A1"
        Do While f <> ""
            r = r + 1
            Range("A" & r) = GetValue(p, f, s, a)
            f = Dir()
        Loop
    End Sub
    Regards, Warship
    Excel 2007 | 2010 on XP | Vista | 7 | 8.1

    We learn by teaching... Please complete the learning curve with feedback.

    Get Lost... Support Search & Rescue.

    Frustration is due to lack of knowledge. Lack of knowledge is due to frustration. Relax.

    Quandary: How is it that the Calendar is moving so fast when the Clock is moving so slow?

    VBA BASICS: http://www.contextures.com/xlvba01.html

    ALWAYS USE A COPY OF YOUR FILE WHEN TESTING CODE !

  9. #9
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,454

    Default Re: Sum cells from multiple files in same folder

    Same method as Warship's code:
    Code:
    Sub DirFiles()
        Dim FileName As String, FileSpec As String, FileFolder As String
        Dim wb As Workbook
        Dim dblSum As Double, fn As String
        
        dblSum = 0
         
        FileFolder = ThisWorkbook.path
        FileSpec = FileFolder & "\test*.*"
         
        FileName = Dir(FileSpec)
        If FileName = "" Then Exit Sub
         
         '   Loop until no more matching files are found
        Do While FileName <> ""
            If IsWorkbookOpen(FileName) = False Then
                'Set wb = Workbooks.Add(FileFolder & FileName)
                'DoEvents
                dblSum = dblSum + (GetInfoFromClosedFile(FileFolder, FileName, "Sheet1", "A1"))
                'wb.Close True
            End If
            FileName = Dir()
        Loop
        
        MsgBox dblSum
    End Sub
     
    Function IsWorkbookOpen(stName As String) As Boolean
        Dim Wkb As Workbook
        On Error Resume Next ' In Case it isn't Open
        Set Wkb = Workbooks(stName)
        If Not Wkb Is Nothing Then IsWorkbookOpen = True
         'Boolean Function assumed To be False unless Set To True
    End Function
    
    '=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
    '    wbPath = "d:\files"
    '    wbName = "budget.xls"
    '    wsName = "Sheet1"
    '    cellRef = "A1:R30"
    Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
        Dim arg As String
        GetInfoFromClosedFile = ""
        If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
        If Not FileExists(wbPath & "\" & wbName) Then Exit Function
        arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
        On Error Resume Next
        GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
    End Function
    
    Function FileExists(sFilename As String) As Boolean
      Dim fso As Object, tf As Boolean
      Set fso = CreateObject("Scripting.FileSystemObject")
      tf = fso.FileExists(sFilename)
      Set fso = Nothing
      FileExists = tf
    End Function

  10. #10

    Default Re: Sum cells from multiple files in same folder

    I found this page when I Google the same problem, it works for me, and thanks a lot, Warship.

    Just want to highlight, to get the code works, the name of worksheet in the active workbook should be the same as multi-files. In this case, It should be named as "Sheet1". (I am very new to VBA, not sure if this is a obvious thing in VBA world, :P).

    I have two extra questions to ask:

    1. Is that possible to pull out from different cell in the same worksheet? In the case, only value in A1 in multi-files are pulled to column A of the active worksheet, what if I wish to pull B1 at the same time to column B?

    2. What sequence the VBA code used to handle multi-files (pulling sequence)? In my case, I pull "date" data from multi-files, and I found that the arrangement of pulling result is not aligned with the file name from A to Z.

    Appreciate for your kindly help!

    Regards,
    ZM


    Quote Originally Posted by Warship View Post
    Place in Std Module and Call LoopThruBooks
    Code:
    Private Function GetValue(path, file, sheet, ref)
        Dim arg As String
        If Right(path, 1) <> "\" Then path = path & "\"
        arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(ref).Range("A1").Address(, , xlR1C1)
        GetValue = ExecuteExcel4Macro(arg)
    End Function
    
    Sub LoopThruBooks()
        Dim p, f, s, a, r
        p = "C:\excelfolder\"
        f = Dir(p & "*.xls")
        Do While f <> ""
            s = "Sheet1"
            a = "A1"
            r = r + 1
            Range("A" & r) = GetValue(p, f, s, a)
            f = Dir()
        Loop
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com