Getting Summary Data From A large Data of Files in a Directory

[Guest]

I have a large number of files in a Directory that are stored with names
Agent100, Agent102,Agent 105, etc.

In those file names I have rows A:E filled with Data Variable rows for
Every File
ie file name Agent100
A (date) b(agent) C(Sell Value) D(profit)
Those reports run weekly and produce hundreds of subfiles like the one above

What I need is to open those files names and get the summary for every one
of those files and construct a file for a given range that will have

B(Agent) C(SUmmary of All Cell Values in that file) D(Summary of Profit in
that Given File



Thank you

 

[Guest]

See if this code will help. Modify as necessary.


Sub getfiles()

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder("C:\temp\") <= changge code as needed

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
If InStr(sf, "Agent") Then

Set fso1 = CreateObject _
("Scripting.FileSystemObject")
Set folder1 = _
fso1.GetFolder(sf)
If folder1.Files.Count > 0 Then
For Each file In folder1.Files

'add code to open each file here.
Next file
End If
End If
Next sf
End If

End Sub

 

[Guest]

Thanks joel But it did not resolve the issues.
Each of the file has different lines and I need to open the file get at the
end do a Summary on each file on sales and profits and then get this figure
to a master file.

 

[Guest]

The last row of a workshseet can be found using the following code

LastRow = Cells(Rows.Count,"A").End(Xlup).Row

Rows.Count = 65536, it is a VBA constant
xlup will move from the last row of the worksheet to find a data in column
A and stop.