Perform action on every workbook in a directory

[Btibert]

Hello everyone,

All of my workbooks in a directory have the same format. As it stands now, I open each one manually, and have my code extract the data from a sheet and aggregate it on the appropriate sheet of my report file.

All I am looking to do is open each workbook, perform my macro, close the work book and open the next. I figure to do this correctly, the macro would have to count the workbooks in the directory (there are 51), and from 1-n, open work book, call my macro, close workbook, next n.

Can someone show me how to do this, if it is is even possible?

Many thanks in advance,

Brock

[tony h]

Sub a()
Dim wkb As Workbook
Dim strPath As String
Dim strFile As String

strPath = "C:\myfiles\" your path
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
Set wkb = Workbooks.Open(strPath & strFile)
'do your bits
wkb.Close savechanges:=True
strFile = Dir()
Loop
MsgBox "done"
End Sub

regards

[Btibert]

Thats perfect, thank you very much.