vba to check presence of file and open/not open

F

fLiPMoD£

Hi,
The problem:- vba to check the presence of a file then prompt user if file
is to be opened or not.
For some reason, when i click no, the program still open the file.

Your help will be very much appreciated.

Sub PegaToday()
Dim sFile As String

sFile = Dir("O:\MRS_reports\Setts\pega_outstanding_" & Format(Date,
"yyyymmdd") & ".xls")
If sFile <> "" Then
MsgBox "Todays Pege is Ready" & vbCrLf & "" & vbCrLf _
& "Do You want to Open?", vbYesNo, "Ready or Not"
If vbYes Then
Workbooks.Open sFile
ElseIf vbNo Then Exit Sub
End If

End If
End Sub

....Comin' From Where I'm From
 
D

Dave Peterson

You want to check to see which button the user clicked. One way is to use a
variable to represent that response:

Option Explicit

Sub PegaToday()
Dim sFile As String
Dim resp As Long

sFile = Dir("O:\MRS_reports\Setts\pega_outstanding_" _
& Format(Date, "yyyymmdd") & ".xls")
If sFile <> "" Then
resp = MsgBox("Todays Pege is Ready" & vbCrLf & "" & vbCrLf _
& "Do You want to Open?", vbYesNo, "Ready or Not")
If resp = vbYes Then
Workbooks.Open sFile
Else
'if they can only answer yes/no, then we don't need the
'else if. If we get here, they had to click No.
Exit Sub
End If
End If
End Sub
 
D

Davie

Try

Sub PegaToday()
Dim sFile As String

sFile = Dir("O:\MRS_reports\Setts\pega_outstanding_" & Format(Date,
"yyyymmdd") & ".xls")
If sFile <> "" Then
If MsgBox ("Todays Pege is Ready" & vbCrLf & "" & vbCrLf _
& "Do You want to Open?", vbYesNo, "Ready or Not") = vbYes Then
Workbooks.Open sFile
ElseIf vbNo Then Exit Sub
End If

End If
End Sub
 
Top