Sum cells from multiple files in same folder

[tripp]

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]

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

 

[hawkzeng]

Jamilm, Thanks a lot, find some good example for this function,

 

[jamilm]

you are welcome. glad to help.

Jamilm, Thanks a lot, find some good example for this function,

 

[ram8gig]

same scenario here but i cant execute the code/script mentioned. i would be glad if someone could help. Thank you!

 

[Ahmed_Essam]

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.

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]

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]

Without code:

final

*	G

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:198px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]78[/TD]
[TD="align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]79[/TD]
[TD="align: center"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]80[/TD]
[TD="align: center"]76[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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

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

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]

Windows has many views for order. What is your definition? If alphabetical, a sort is easy enough.

 

[Bond00]

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!