Each week I receive a workbook from one of my customers. It contains mutliple
sheets with salesnumbers from all the branch of my customer. Each sheet
contains from 1 000 to 15 000 lines of salesdata. Every article sold gets one
line in the sheets.

Each workbook are named "weeknr.xls" and are stored in the same folder.

Now my question/problem. I need to gather all sales info for one branch on
one article from all the weeks.

I know how to get the data from one workbook to another, but how do I get it
to scan all the workbooks in the folder.

Any ideas.

Dim oFSO

Sub LoopFolders()
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\MyTest")

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
'now do your stuff
End If
Next file

Set oFSO = Nothing

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lupus" <(E-Mail Removed)> wrote in message
news:5564D9B1-4849-4834-9515-(E-Mail Removed)...
> Each week I receive a workbook from one of my customers. It contains
mutliple
> sheets with salesnumbers from all the branch of my customer. Each sheet
> contains from 1 000 to 15 000 lines of salesdata. Every article sold gets
one
> line in the sheets.
>
> Each workbook are named "weeknr.xls" and are stored in the same folder.
>
> Now my question/problem. I need to gather all sales info for one branch on
> one article from all the weeks.
>
> I know how to get the data from one workbook to another, but how do I get
it
> to scan all the workbooks in the folder.
>
> Any ideas.

I want to add this to Bob's reply

If your workbook is used in different language versions you can test for the extension
See http://www.rondebruin.nl/fso.htm

Type = "Microsoft Excel Worksheet" is not working then


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Phillips" <(E-Mail Removed)> wrote in message news:u$(E-Mail Removed)...
> Dim oFSO
>
> Sub LoopFolders()
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> Dim fldr
>
> Set oFSO = CreateObject("Scripting.FileSystemObject")
>
> Set Folder = oFSO.GetFolder("c:\MyTest")
>
> For Each fldr In Folder.Subfolders
> selectFiles fldr.Path
> Next fldr
>
> For Each file In Folder.Files
> If file.Type = "Microsoft Excel Worksheet" Then
> Workbooks.Open Filename:=file.Path
> 'now do your stuff
> End If
> Next file
>
> Set oFSO = Nothing
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Lupus" <(E-Mail Removed)> wrote in message
> news:5564D9B1-4849-4834-9515-(E-Mail Removed)...
>> Each week I receive a workbook from one of my customers. It contains
> mutliple
>> sheets with salesnumbers from all the branch of my customer. Each sheet
>> contains from 1 000 to 15 000 lines of salesdata. Every article sold gets
> one
>> line in the sheets.
>>
>> Each workbook are named "weeknr.xls" and are stored in the same folder.
>>
>> Now my question/problem. I need to gather all sales info for one branch on
>> one article from all the weeks.
>>
>> I know how to get the data from one workbook to another, but how do I get
> it
>> to scan all the workbooks in the folder.
>>
>> Any ideas.
>
>

Thank you both for rapid response. I'm going to try it during the day.

Lupus

"Ron de Bruin" wrote:

> I want to add this to Bob's reply
>
> If your workbook is used in different language versions you can test for the extension
> See http://www.rondebruin.nl/fso.htm
>
> Type = "Microsoft Excel Worksheet" is not working then
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message news:u$(E-Mail Removed)...
> > Dim oFSO
> >
> > Sub LoopFolders()
> > Dim Folder As Object
> > Dim Files As Object
> > Dim file As Object
> > Dim fldr
> >
> > Set oFSO = CreateObject("Scripting.FileSystemObject")
> >
> > Set Folder = oFSO.GetFolder("c:\MyTest")
> >
> > For Each fldr In Folder.Subfolders
> > selectFiles fldr.Path
> > Next fldr
> >
> > For Each file In Folder.Files
> > If file.Type = "Microsoft Excel Worksheet" Then
> > Workbooks.Open Filename:=file.Path
> > 'now do your stuff
> > End If
> > Next file
> >
> > Set oFSO = Nothing
> >
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Lupus" <(E-Mail Removed)> wrote in message
> > news:5564D9B1-4849-4834-9515-(E-Mail Removed)...
> >> Each week I receive a workbook from one of my customers. It contains
> > mutliple
> >> sheets with salesnumbers from all the branch of my customer. Each sheet
> >> contains from 1 000 to 15 000 lines of salesdata. Every article sold gets
> > one
> >> line in the sheets.
> >>
> >> Each workbook are named "weeknr.xls" and are stored in the same folder.
> >>
> >> Now my question/problem. I need to gather all sales info for one branch on
> >> one article from all the weeks.
> >>
> >> I know how to get the data from one workbook to another, but how do I get
> > it
> >> to scan all the workbooks in the folder.
> >>
> >> Any ideas.
> >
> >
>
>
>