Search The Web

Today's Headlines

Friday, April 29, 2011

Microsoft Access Tips & Tricks: Opening Files From Within Access

One of the important things I need to do from within my EBooks database is opening and reading files once I have figured out what I want to read. There are at least three ways to do this from within Microsoft Access, and in this post, I will examine all three ways to do it. Even though I use this capability only to read EBooks on my computer, other uses for this capability are easy to think of. For instance, you might want to catalog your videos or photographs in an Access database and have the ability to open them when needed from inside the database itself. In general, any files you can open on the computer you can open from inside Microsoft Access using the techniques in this post. With a couple of the techniques posted here, you can also run any program installed on your computer from within Access.
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.
There are three main techniques that can be used to open files from inside Microsoft Access. The first of these techniques involves the use of the Shell() function. The Shell() function can not only be used to open files, but can also be used to run any program installed on the computer. For instance, you can not only use Shell() to open a text file in Notepad, but you can also simply open Notepad with no file loaded. This can be useful because you might require the functionality to allow the user to enter notes into some application like Notepad, and save it on the computer. Or you can have Shell() open the Calculator program so that the user can perform some calculations and enter the result into some form in Microsoft Access.

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

End If
End Sub

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)
End Sub

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"
       End If
       MsgBox "No file location entered!", vbExclamation, "Can not open file"
   End If
End Sub 

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!


Las Vegas Foreclosures said...
This comment has been removed by a blog administrator.
Blogannath said...
This comment has been removed by the author.
Kuba Jewgieniew said...

I consider something really interesting about your blog so I bookmarked.I truly appreciate your work, Great post.

Kuba Jewgieniew said...

I hope it’s ok I added your site to a few of my bookmark lists. I just wanted to comment to show my appreciation for your article as it is very enticing.

Blogannath said...

No problem. I appreciate your interest in my blog. At least I know I am not wasting my time entirely!

Cavite Web Design said...

Great , I believe people should learn a lot from this website its really user friendly.

Baltimore Nightlife said...

I like this site and it is very informative and hence people who will come across this site will gain lot of information about it. I am very to being glad to be part of this site! I will visit this site in future too.

Anonymous said...

You can use the shell and reference "C:\Windows\Explorer.exe" as the program you want to open your file. This will open the file with the default program and save some headache.

Blogannath said...

Thank you for that tip! I just verified that it does indeed work to open a file in the default program (as set in the OS). Thanks again.

Cautity38 said...

I found your blog when I was looking for a different sort of information but I was very happy and glad to read through your blog. The information available here is great.

nevergiveupPH said...

This is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion. You also know how to make people rally behind it, obviously from the responses. Youve got a design here thats not too flashy, but makes a statement as big as what youre saying. Great job, indeed.

Anonymous said...

Thank you so much for this article. It was a tremendous help to a problem I was trying to solv.e

Simon Jooste said...

I'm learning so much from people like you. Thank you. But I still have no idea where "C:\Windows\Explorer.exe" should go in your code and what, if anything, should be dropped.


Blogannath said...

Basically, instead of checking for extensions and using different programs to open files with different extensions, you would use explorer.exe to open any of them, and they would all open in the default program defined in the OS for that extension. Something like this:

Public Sub OpenUsingShell(MyFileName as String)
Dim RetVal as Integer

RetVal = Shell("C:\Windows\Explorer.exe " & MyFileName, vbNormalFocus)

End Sub

Anonymous said...

what would be the code for just let a click on a button to locate a file in the computer (or network) and place the path together with the filename in the text box for subsequent action?


Blogannath said...

Have you looked at the post on Parsing File Names ( If you can't find what you are looking for there, please post a comment with specifics of your problem and I will try to take a look.

Jack Maple said...

Well written article - pleasure to read and done in a measured tone which takes into account how the usual Joe absorbs information and preps us to do something useful with it. Good luck with your future endeavours Ian Goslin.

Paul Woodruff said...

I know this is an old post, but the topic is still fresh.

In MS-Access, I am using this code to open a PowerPoint presentation. However, I want my code to WAIT until the PowerPoint presentation is closed by the user before executing the next line of VBA code.

Any thoughts on how I can test to see if the shell is still running before continuing with the next line of code?

Blogannath said...

Hi Paul, I can't think of any way to make the VBA program wait until the opened program is closed. That is just the way Windows works - it allows multiple programs to work concurrently. But you can rig up a kludgy way to accomplish this by popping up a prompt and ask the user to click OK on that prompt only after closing the presentation. Your program can be set to wait for the user to click OK before continuing execution.

Visitors Country Map

Free counters!

Content From

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!



Spelling Bee
difficulty level:
score: -
please wait...
spell the word:

Search The Web