Airborne
11-17-2004, 07:55 AM
Hi, I have a project where I search for files and if they are found they are opened. If they are not found nothing happens leving people wandering if anything happened. I want a msgbox telling people "no files found". Now in the code I've tried to implement a msgbox but because it's looking for multiple files the msgbox pops up a few times. I only want it to pop up when the search is finished. Here is the code
Sub OpenFiles4(fName As String)
Dim mybook As Workbook
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\Data\Year\ (file://\DataYear)" & sDirectory5 & "" & sDirectory4
.SearchSubFolders = True
.FileName = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
'I put this in when no files were found
Else
If .Execute = 0 Then
MsgBox fName & " not found!"
End If
End If
Application.ShowWindowsInTaskbar = True
End With
End If
End Sub
Sub GetAllFiles4()
Application.ScreenUpdating = False
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_1_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_2_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_3_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_4_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_5_N.xls")
Windows("Data1").Activate
End Sub
So if only ....._5_N.xls was found I get 4 msgboxes first.:wot
Regards.
Sub OpenFiles4(fName As String)
Dim mybook As Workbook
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\Data\Year\ (file://\DataYear)" & sDirectory5 & "" & sDirectory4
.SearchSubFolders = True
.FileName = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
'I put this in when no files were found
Else
If .Execute = 0 Then
MsgBox fName & " not found!"
End If
End If
Application.ShowWindowsInTaskbar = True
End With
End If
End Sub
Sub GetAllFiles4()
Application.ScreenUpdating = False
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_1_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_2_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_3_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_4_N.xls")
Windows("Data1").Activate
Call OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_5_N.xls")
Windows("Data1").Activate
End Sub
So if only ....._5_N.xls was found I get 4 msgboxes first.:wot
Regards.