Data from multiple sheets and workbooks

[Guest]

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.

 

[Bob Phillips]

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)

 

[Ron de Bruin]

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

 

[Guest]

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

Lupus