How to display a message if expected shell document doesn't exist 1

[SelfTaughtVBA]

As the last remaining feature to include before publishing an .xls file for colleagues to use, I would love to learn how to show a warning message if the following macro (yes, I did find the basic gist for it via Google) fails to find the expected document:

General Declarations bit...

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub OpenPPT()

Dim PPT_Doc As String

PPT_Doc = ActiveWorkbook.Path & "\" & "Trends.ppt"
ShellExecute 0, "open", PPT_Doc, vbNullString, vbNullString, 5

End Sub

This macro is called from a button, and at the moment if the document doesn't exist then no clue is given to the User other than "nothing happening".

I have checked whether Err holds a value to see if I can use that in an IF clause, but it doesn't (well, it's zero whether a Powerpoint document is found to open or not).

My handle tells all that I am doing this for fun to solve a MIS need but am by no means a VBA pro; Learning VBA has become a drug though, so having failed to solve the problem myself I would love to learn from someone else.

Grateful thanks.

 

[PHV]

A starting point:
If Dir(PPT_Doc) = "" Then
MsgBox "warning message"
Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SelfTaughtVBA]

Many thanks, PHV.

Not only did you reply with the speed of light but solved the problem.

Grateful thanks, and at 17:18 UK time I have by 12 minutes kept my indication (it wasn't a promise!) that I'd have the work completed before close of play today.

 

[rmikesmith]

You could also inspect the return value of the ShellExecute function, detecting other potential error conditions besides a missing document file. Example, using your code:

Const ERROR_FILE_NOT_FOUND = 2&
Const ERROR_PATH_NOT_FOUND = 3&
Const SE_ERR_NOASSOC = 31&

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub OpenPPT()

Dim PPT_Doc As String
Dim lReturn as long
Dim sMsg as String

PPT_Doc = ActiveWorkbook.Path & "\" & "Trends.ppt"
lReturn = ShellExecute(0, "open", PPT_Doc, vbNullString, vbNullString, 5)

If lReturn <= 32 Then
  Select Case lReturn
  Case ERROR_FILE_NOT_FOUND
    sMsg = "File not found"
  Case ERROR_PATH_NOT_FOUND
    sMsg = "Specified directory path not found"
  Case SE_ERR_NOASSOC
    sMsg = "No application associated with the given file extension"
  [COLOR=green]'... other error cases[/color]

  Case Else
    sMsg = "Unknown Error!"
  End Select
   
  MsgBox sMsg, vbExclamation + vbOKOnly
End If

End Sub

The Win32 API documentation gives additional error codes.

Regards,
Mike

 

[SelfTaughtVBA]

Magic, thanks, Mike; although PHV's reply was perfect for my need this time (no need to distinguish different failure causes) you have shared some most useful advice to the benefit of another project I will shortly start.