Extracting Data from many worksheets

Thank you for your question.

The command you need is Dir which returns filenames. The first time you use it you specify the location and mask and then you just use dir to find the next match. When dir finds no more files it returns an empty string.

Example

Sub ReportFiles()



Dim FoundFile As String

'Find first file

FoundFile = Dir("c:\reports\*.xls")

Do While FoundFile > ""

    Workbooks.Open FileName:="c:\reports\" & FoundFile

    ' Process each file

    '

    ' Get name of next file

    FileName = Dir

Loop



End Sub