If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), flattening a table (using VBA), cascading comboboxes, and parsing file names.
The syntax of the Shell() function is quite simple. It requires a string argument that denotes the program to be opened, and an optional argument that specifies the window style. You can refer to the Access help files for the syntax, and also the possible values for window style.
To open the calculator program, for instance, you would use the following lines of code:
Dim RetVal as Integer
RetVal = Shell("C:\Windows\calc.exe", vbNormalFocus)
If the Shell() function successfully executes the named file, it returns the task ID of the started program.
How do you use Shell() to open files from within Access? In this previous post on parsing file names, I have provided the code that will enable you to find the file extension of a given file. The program that should be used for opening a file depends on the file extension in most cases. So, one needs to use a series of if ... else if ... switches to check the value of the extension, and open the file using the appropriate program. A snippet of such code is provided below:
Public Sub OpenUsingShell(MyFileName as String)
Dim RetVal as Integer
Dim Extension as String
Extension = ExtractExtension(MyFileName)
If (Ucase(Extension) = "TXT") then
RetVal = Shell("C:\Windows\notepad.exe " & MyFileName, vbNormalFocus)
ElseIf (Ucase(Extension) = "DOC") then
'Code for invoking Microsoft Word to open the file goes here
'Other checks for other file extensions go here and so on
As you can see, the use of Shell() is somewhat inconvenient because you need to specify the program you want to invoke to open each file that you want to open. So, your code can grow quite large if you are dealing with lots of different types of files that require lots of different programs to open. However, it gives you, the programmer, complete control over what program you want to invoke to open any given document or other file on your computer. Even if a file type is registered in the operating system to be opened by one particular programmer, you can invoke some other program to open it.
You can use different programs to open the exact same type of file depending on any number of criteria such as the file path, file name, etc. Also depending on how the file opening is invoked, you can open the file in different programs (one for read-only access, one for editing, etc.). That makes Shell() very powerful even though it can be a little inconvenient to use. You can also use Shell() without a file name to simply run any program that is installed on your computer (as illustrated in the example using calc.exe above).
The next technique that I will explain is a lot easier to use than Shell(). This is the Application.Followhyperlink() function. Using Application.Followhyperlink() is simplicity itself. Consider the snippet of code below:
Public Sub OpenUsingHyperlink(MyFileName as String)
If the computer knows how to handle the type of file that is passed to Application.FollowHyperLink(), it will simply open the file using the default method for handling such a file. That is all there is to it.
However, there are some disadvantages to using this method. First and foremost, Application.FollowHyperLink() is provided to open links over the internet, not necessarily open local files on the computer. Because of this, the computer treats any file opened using this method as a download from the internet, and therefore, inherently unsafe. Many types of files, when opened using this method, will trigger a security warning that you have to dismiss before the file will open up. This is not a warning produced by Access (it is produced by the operating system), so using DoCmd.SetWarnings False before Application.Followhyperlink() will not get rid of these warnings.
Moreover, the default application used to open files in this way can be different from what you would open the file in by double-clicking on it on your computer. That is because the computer thinks you are opening the file off the internet, so it opens the file in the default application for that file as seen by your internet browser. So, for instance, image files may open up in your browser instead of in your favorite image-editing or viewing software because, as far as the browser is concerned, an image on the internet is to be handled by the browser itself rather than being passed off to your image editing program.
Also, if the file you pass to Application.FollowHyperLInk() does not exist, or if there is no program associated with the file type in your browser, there is no warning of any sort. The command simply fails silently, without producing either the desired result or an error message. So, you have to be careful when using the method, in spite of its simplicity. Until recently, this was my usual method of opening files from within Access, but I have now switched to the third method detailed below.
The third method involves the use of the ShellExecute API. It is a very powerful method that has all the advantages of both the methods described above, but none of the disadvantages. However, with power comes complexity. You can read about some of the parameters that the program takes, as well as return values that it produces at this link. The code snippet below should also help clarify things a bit.
This is the code behind a form in my EBooks database application that allows me to browse through the books in the catalog. In the form, the field BookLocation contains the file name corresponding to a given book along with a relative path to the file. There is a button on the form called Read that, when clicked, invokes the ShellExecute API to open the file and allows me to read the selected book. The ShellExecute API works only with full paths, so the subroutine below converts the relative path to a full path, and then calls the ShellExecute function.
Private Declare Function apiShellExecute 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 'You have to declare the function because it is not a native Microsoft Access function. Rather it is a Visual Basic function.
Private Sub CmdRead_Click()
Dim RetVal As Long
Dim TaskID As Variant
Dim FullFilePath As String
On Error Resume Next
If Not IsNull(Me.BookLocation) Then
FullFilePath = Replace(Me.BookLocation, ".\", CurrentProject.Path & "\")
RetVal = apiShellExecute(hWndAccessApp, "open", FullFilePath, vbNullString, vbNullString, vbNormalFocus)
If RetVal = 0 Then '0 indicates not enough memory
MsgBox "The operating system is out of memory or resources.", vbExclamation, "File Open Failure"
ElseIf RetVal = 2 Then '2 indicates that the file was not found
MsgBox "The specified file was not found.", vbExclamation, "File Open Failure"
ElseIf RetVal = 3 Then '3 indicates that the path was not found
MsgBox "The specified path was not found.", vbExclamation, "File Open Failure"
ElseIf RetVal = 11 Then '11 indicates that the executable is corrupted
MsgBox "The .exe file is invalid (non-Win32 .exe or error in .exe image).", vbExclamation, "File Open Failure"
ElseIf RetVal = 31 Then 'If the association is missing, then use Shell to try the OpenWith dialog
TaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " & FullFilePath, vbNormalFocus)
ElseIf RetVal <= 32 Then 'the apishellexecute function returns a value greater than 32 on success
MsgBox "File could not be opened. Error number " & CStr(RetVal), vbExclamation, "File Open Failure"
MsgBox "No file location entered!", vbExclamation, "Can not open file"
I have checked for some of the possible error messages that the ShellExecute API may produce (I think these are the most common errors that are likely to occur), but there are several more as you can see at the link I referred to earlier.
What is special about the ShellExecute API function? As I mentioned earlier, it combines the advantages of the two previous method. It gives you convenience because you can call the function as I have done above, using just the file name. The function automatically invokes the program that is registered with the operating system to handle the file type. You do not have to check file extensions and decide which program to invoke. Moreover, the program that is invoked automatically is the one registered with the operating system, not the one associated with the file extension in your browser. So, there are no surprises with images opening up in your browser, etc.
But, you can actually use the function to specify the program to use to open the file also. Simply use syntax as in the Shell() command to pass the program path along with the file path, and the ShellExecute API will open the file in that program. You can use program switches and options also to control precisely how the file is opened. This gives you the ability, for instance, to open the same file in multiple programs depending on which button is clicked in your Microsoft Access form - one for viewing the file, a different one for editing the file, etc.
In addition, you can print the file directly using the ShellExecute API instead of opening it simply by changing the "open" in the arguments passed to the ShellExecute API to "print". Moreover, since the operating system knows that the file being opened is a local file, it will not complain, and put up warning messages. If it knows how to open the file, it will open it immediately. More importantly, if it does not, it will not fail silently. Instead, it will produce an error message (you just have to make sure you check for all return values less than or equal to 32, and produce an appropriate error message for the user of your program).
Hope this post has been helpful in solving any problems you might have had with opening external files from within Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!