PDA

View Full Version : [SOLVED] Multiple messages



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.

Ken Puls
11-17-2004, 08:11 AM
Hi again, Airborne!

What about something like this (untested)


Sub OpenFiles4(fName As String)
Dim mybook As Workbook, dim filecount as integer
On Error Resume Next
filecount = 0
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))
filecount = filecount + 1
'I put this in when no files were found
End If
End If
Application.ShowWindowsInTaskbar = True
End With
End If
if filecount = 0 then MsgBox "No files not found!"
End Sub

HTH,

Airborne
11-17-2004, 08:47 AM
Hi Ken, long time not seen VBA from you:wot (pardon my English):) . About the code...it gives me the same result. I get four msgboxes telling me no files found.

Is it because Sub GetAllFiles4() is a loop? It starts from there.

Thanks and Regards.

Ken Puls
11-17-2004, 09:25 AM
Sorry Airborne! I read that really quick before breakfast!

Yes, that would be it. I didn't realize that.

I looked at your first proc, and figured that it was because the msgbox was in the loop.

How about this approach instead... take the msgbox out of the OpenFiles4 routine all together. Move the counter portion (that I added) so that it counts the number of opened files in the GetAllFiles4 routine, then put the messagebox in there.

Sorry, I can't give you some code at the moment. I'm in the middle of a major systems upgrade today, but will try back if I can!

Cheers,

Airborne
11-17-2004, 04:33 PM
No problem Ken. I've tried your suggestion though but then I always get the message, if it finds files or not.:thinking:


Regards.

Kieran
11-17-2004, 05:56 PM
Airborne

This is untested but try


Function OpenFiles4(fName As String) As Boolean
Dim mybook As Workbook
OpenFiles4 = False
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))
OpenFiles4 = True
'I put this in when no files were found
End If
Application.ShowWindowsInTaskbar = True
End With
End If
End Function

Sub GetAllFiles4()
Dim Result As Boolean
Dim Found As Boolean
Found = False
Application.ScreenUpdating = False
If OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_1_N.xls") Then
Windows("Data1").Activate
Found = True
End If
If OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_2_N.xls") Then
Windows("Data1").Activate
Found = True
End If
If OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_3_N.xls") Then
Windows("Data1").Activate
Found = True
End If
If OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_4_N.xls") Then
Windows("Data1").Activate
Found = True
End If
If OpenFiles4("Data" & Sheets("Spare").[B2].Value & "-" & Sheets("Spare").[C2].Value & "-" & Sheets("Spare").[D2].Value & "_5_N.xls") Then
Windows("Data1").Activate
Found = True
End If
If Found = fase Then
MsgBox "No files were found to open"
End If
End Sub

Airborne
11-17-2004, 07:51 PM
:yes Kieran, tested or not..it works! Great, thanks for the help.

Regards.