vba to check presence of file and open/not open

  • Thread starter Thread starter fLiPMoD£
  • Start date Start date
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
 
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
 
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
 
Back
Top