In Opening Different File Types, I described the FollowHyperlink method for opening non-Excel files in their native application (or whichever application you have set to open them). I love that method, but every time I mention it someone suggests ShellExecute. There must be something to it.
The declaration looks like this:
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
The arguments are:
- hwnd – a Long that is the handle to a parent window. Not the parent window, I guess, just any parent window that can receive error messages returned by the API. Get hwnd using the FindWindow API.
- lpOperation – This is what you want to do, in the form of a null terminated string. You can ‘open’ or ‘print’ and a few other things depending on the object you’re doing it to. For instance, you can ‘explore’ a folder, but not a file.
- lpFile – the full path to the file name or directory name.
- lpParameters – This is only for executable files and must be null terminated. That’s pretty much all I know about this one. It’s vbNull for everything I do.
- lpDirectory – You can specify the default directory, but I’m not sure why you’d want to.
- nShowCmd – Common uses are SHOWNORMAL (1), SHOWMINIMIZED (2), SHOWMAXIMIZED(3).
The “cool” way to use this API is to wrap it in your own function. For instance, if you want to explore a folder create a function like this:
Dim lResp As Long
Dim lHwnd As Long
lHwnd = FindWindow(“XLMAIN”, vbNullString)
lResp = ShellExecute(lHwnd, _
“explore” & vbNullChar, _
sFullPath & vbNullChar, _
vbNull, vbNull, vbNull)
FldrExplore = (lResp > 32)
End Function
You can take a lot of the complexity out using the API by wrapping it in a function like this. The function doesn’t have the flexibility of using the API directly because certain arguments are hardcoded, like “explore”. A couple of different functions that only use one aspect of ShellExecute can make your code more readable. Here’s a function to open a file
ByRef sErrMsg As String) As Boolean
Dim lResp As Long
Dim lHwnd As Long
lHwnd = FindWindow(“XLMAIN”, vbNullString)
lResp = ShellExecute(lHwnd, _
“open” & vbNullChar, sFullPath & vbNullChar, _
vbNull, vbNull, SW_SHOWMAXIMIZED)
If lResp = ERROR_FILE_NOT_FOUND Then
sErrMsg = “File not found.”
End If
OpenDocument = (lResp > 32)
End Function
This function I stole. So if he wants credit for it, he better speak up (I know he’s reading). It uses a constant to test for a specific error and passes the error message (sErrMsg) back to whatever called the function. You could use a Select Case statement to test for some common errors in this way.
The API returns an integer. If it’s greater than 32, then it did whatever it was supposed to do. If it’s less than or equal to 32, it didn’t get the job done. Some of the common errors you can test for a below. Create constants to hold the literal values so that your code is more readable.
Public Const ERROR_FILE_NOT_FOUND = 2&
Public Const ERROR_PATH_NOT_FOUND = 3&
Public Const SE_ERR_SHARE = 26
Public Const SE_ERR_NOASSOC = 31
Those who want a simpler method of exploring a folder can use something like this:
Folder = “c:files”
Shell “explorer.exe /e ,” & Folder, vbNormalFocus