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.

 

[Worf]

(Answer to PM)

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

 

[Bond00]

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?

 

[Bond00]

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?

 

[Worf]

Sorting example:

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

 

[Worf]

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.

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

 

[Bond00]

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.

 

[Worf]

D is the column being sorted, note that the other columns change accordingly.

 

[Bond00]

So SortRange "b2:j50", "b" would work the same except sort based on column B's values? pretty neat.

 

[Bond00]

Sorting example:

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.

 

[Kenneth Hobson]

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.

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