Find and open Excel Workbooks

[laurenbattaglia]

I need to be able to find and open multiple workbooks each month. The workbooks will always be in the same folder and the name will always start with "EXP". I can find the files with the code below, I can't figure out how to open each file when it is found.

Private Sub CmdFndFile_Click()
Set fs = Application.FileSearch
Dim f
f = "EXP*"
Dim i As Integer

With fs
.LookIn = "C:\Documents and Settings\lbat\My Documents\lauren"
.FileName = ((f))
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)


Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


Thanks, Lauren

 

[PHV]

Something like this ?
Private Sub CmdFndFile_Click()
Set fs = Application.FileSearch
Dim f
f = "EXP*"
Dim i As Integer

With fs
.LookIn = "C:\Documents and Settings\lbat\My Documents\lauren"
.FileName = ((f))
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
Set xl = CreateObject("Excel.Application")
xl.Visible = True
For i = 1 To .FoundFiles.Count
xl.Workbooks.Open .FoundFiles(i)
Next i
Set xl = Nothing
Else
MsgBox "There were no files found."
End If
End With
End Sub-

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laurenbattaglia]

thanks...that worked perfect.

Lauren