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

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


"stratis" wrote:

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

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.


"Joel" wrote:

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


"stratis" wrote:

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

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.

"stratis" wrote:

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.


"Joel" wrote:

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


"stratis" wrote:

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