MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dynamically Opening All Files in a Directory


Posted by Leslie on December 11, 2001 5:43 PM

How can I open all files in a directory (even when new files have been added) without having to change the visual basic code?


Posted by Bariloche on December 11, 2001 6:46 PM

Leslie,

Try this code:


Dim File() As String
Dim FoundFile As String
Dim FileCount As Integer


Sub ConvertMathCadData()
FoundFile = Dir("U:\SS\Options\*.xls")

FileCount = 1
ReDim Preserve File(FileCount)
File(FileCount) = FoundFile

Do While FoundFile <> ""
FoundFile = Dir()
If FoundFile <> "" Then
FileCount = FileCount + 1
ReDim Preserve File(FileCount)
File(FileCount) = FoundFile
End If
Loop

For i = 1 To FileCount
Workbooks.Open File(i)
Next i

End Sub


Obviouly you'll have to change the "U:\ ..." stuff to something relevant for you, but the rest of it should work ok.


enjoy