vba to check presence of file and open/not open

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

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

"fLiPMoD£" wrote:
>
> 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

--

Dave Peterson

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

fLiPMoD£ wrote in message <#k9R9beSFHA.3944@TK2MSFTNGP10.phx.gbl>...
>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
>
>