Sum cells from multiple files in same folder

tripp

New Member
Joined
May 8, 2006
Messages
24
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.
 

jamilm

Well-known Member
Joined
Jul 21, 2011
Messages
740
Google Pull function that will help you.

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ram8gig

New Member
Joined
Nov 14, 2014
Messages
4
same scenario here but i cant execute the code/script mentioned. i would be glad if someone could help. Thank you!
 

Ahmed_Essam

New Member
Joined
Oct 10, 2016
Messages
1

ADVERTISEMENT

Thanks for the macro. I've been trying to modify the code a bit to do the following:

I want to be able to loop around several consecutive values of a in the same column in this example a="F14" my next value would be a="F15" and increment the range("A" & r) to range("B" & r) and so on with each loop. I'd really appreciate the help. Thanks.

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, i
    p = "D:\SG\addition\"
    f = Dir(p & "*.xlsx")
    s = "Sheet2"
    r = 1
    a = "F14"
    
        Do While f <> ""
            r = r + 1
            Range("A" & r) = GetValue(p, f, s, a)
            f = Dir()
        Loop
    
End Sub
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,651
Office Version
  1. 365
Platform
  1. Windows
I have similar question, I want to learn how to use sum() to add cells from different files. Is that possible without using VB?
Thanks
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,100

ADVERTISEMENT

Without code:

final

*G
7845
7931
8076

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:198px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G78='c:\pub\[erp.xlsm]sheet1'!B10
G79='C:\pub\[uform.xlsm]sheet2'!C2
G80=SUM(G78:G79)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
70
Sorry to bump this older post but I was hoping someone could help me with this issue. I am using this code, and got it working but it doesn't extract the cell info in the order of the files in the folder directory. It seems to enter the input in a random order, although that order is the same every time, I can tell easy because I have the files numbered 1-10.xlsx for testing purposes and also a cell with 1-10 to test it matching up in order. The output is random order but the same order every time, like 1,10, 4.xls etc.. Any help with this is very much appreciated!
If we cant figure out why it wont grab the docs/data in order I need a Sub that will grab the file name in the folder and make it a hyper link in the same output order as this cell info. Right now i use the code at the bottom that enter the info in the right order but the cell info doesn't line up with it.

this is for my cell data

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")
    s = "Sheet1"
    a = "A1"
    Do While f <> ""
        r = r + 1
        Range("B" & r) = GetValue(p, f, s, a)
        f = Dir()
    Loop
End Sub

This is for the file name next to it with hyperlinks
Code:
Sub FileHyperlinks()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\excelfolder\")
i = 1
'Selects the page # to enter values to (doesn't matter what the page name is)
Sheet1.Activate
'loops through each file in the directory
For Each objFile In objFolder.Files

    'select cell (I think the ,1 is column A "1")
    Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
    'create hyperlink in selected cell
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        objFile.path, _
        TextToDisplay:=objFile.Name
    i = i + 1
Next objFile
End Sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,128
Office Version
  1. 365
Platform
  1. Windows
Windows has many views for order. What is your definition? If alphabetical, a sort is easy enough.
 

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
70
Yeah i don't know the syntax too well, i am able to mod code easy enough to what i need but creating it is a different story. :) Could you show me how to add so my first snippet of code above can loop it alphabetical/numerical in order in the Loopthrubooks sub? that's how my folder of files is organized.
thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top