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.
 
(Answer to PM)

What do you want to sort, a single column or several columns using one of them as reference?
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I found something else i was working on that your sort code helped with. I was wondering how to limit the ranges it sorts. I need it to sort from B to J column and from row 2 to 500. is there variables or constants i can set to make it work like that?
 
Upvote 0
Ran into another issue, with my original thing trying to get data from all files in a folder, well when i put that and try to use it on the network share it wont populate. I fixed the directory locations etc so thats right but it wont work. I also allowed macros to run. What else could it be?
 
Upvote 0
Sorting example:

Code:
Sub Main()
SortRange "b2:j50", "d"
End Sub


Sub SortRange(sr$, col$)
' sr = range to sort
' col = reference column
Dim s As Worksheet
Set s = ActiveSheet
s.Sort.SortFields.Clear
s.Sort.SortFields.Add s.Cells(2, col), 0, xlAscending, , 0
With s.Sort
    .SetRange s.Range(sr)
    .Header = xlNo
    .MatchCase = 0
    .Orientation = xlTopToBottom
    .SortMethod = 1
    .Apply
End With
End Sub
 
Upvote 0
Ran into another issue, with my original thing trying to get data from all files in a folder, well when i put that and try to use it on the network share it wont populate. I fixed the directory locations etc so thats right but it wont work. I also allowed macros to run. What else could it be?

o Do you get an error message or it simply does not work?
o Please run the test code below and report the result. Let us try to go stepwise and isolate the issue.


Code:
Sub ListFiles()
' list the first five files.
' goal: determine if the macro can find the files.
Dim fso As Object, fld As Object, f As Object, i%, p$
i = 0
p = "C:\pub\"                                           ' network path here
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(p)
For Each f In fld.Files
    MsgBox f.Name
    i = i + 1
    If i > 4 Then Exit Sub
Next
End Sub
 
Upvote 0
in the above code what is the "d" for?
SortRange "b2:j50", "d"

Also i figured out the issue for that not working over the network, it was because it needed that exact extension for some reason. i was using *.xls" it worked when i made it *.xlsx"
that worked fine before on the local drive but not over the network without the full extension. *.xls*" worked also.
 
Upvote 0
D is the column being sorted, note that the other columns change accordingly.

Z1YaV52.jpg
 
Last edited:
Upvote 0
So SortRange "b2:j50", "b" would work the same except sort based on column B's values? pretty neat.
 
Upvote 0
Sorting example:

Code:
Sub Main()
SortRange "b2:j50", "d"
End Sub


Sub SortRange(sr$, col$)
' sr = range to sort
' col = reference column
Dim s As Worksheet
Set s = ActiveSheet
s.Sort.SortFields.Clear
s.Sort.SortFields.Add s.Cells(2, col), 0, xlAscending, , 0
With s.Sort
    .SetRange s.Range(sr)
    .Header = xlNo
    .MatchCase = 0
    .Orientation = xlTopToBottom
    .SortMethod = 1
    .Apply
End With
End Sub

How did it work before? did it just sort based on the order of the file names? in my test the files names were numbered so it didn't matter but in the real environment the file names could be anything and may not line up alphabetically with any column in the table. I need the items to sort based on the order of the files in the folder not by one of the columns of data.
 
Upvote 0
I still don't see what file sort order has to do with this 7+ year old thread. I gave you a routine to order/sort which orders by Name or Date either ascending or descending.

As I explained before, I doubt that you know, I know I don't, what order the files are stored. Likely, it is the order that fso is retrieving it. FSO does allow you to get file properties like date created, modified, accessed, and many more. If you are going that route, you need to add the filename and/or the column to sort by with that property value. Then sort as usual.

VBA offers one function that returns a FileDateTime(). As help explains, it is either the date created or modified depending on the system. e.g.
Code:
Sub Test_fdt()
  [A1] = ThisWorkbook.FullName
  [B1] = fdt([A1])
  [B1].NumberFormat = "mm/dd/yyyy h:mm am/pm"
End Sub

'=fdt(A1)
Function fdt(aFile As String) As Double
  fdt = FileDateTime(aFile)
End Function
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

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
Back
Top