Opening Files From Access
There's a few ways to do open external files in VBA: Application.FollowHyperlink, the Shell() function, and the ShellExecute API. Each have their own characteristics... lets take a closer look.
Application.FollowHyperlink "C:\Documents and Settings\User\SomeFile.pdf"
Application.Followhyperlink is a simple, easy-to use way to open files from within Access. But it does have a few downfalls.
This method was designed for navigating to webpages (as you might see from looking at the optional argument list in the VBA help files). As we know, there are a great many files that can be opened from the web, but, it comes at a price. Any file that is passed to the OS in this manner is treated as if it were randomly pulled off the web. This means that there's some security measure to get through.
Often, when utilizing the Application.FollowHyperlink method, we will recieve warnings about opening certain types of files that may be harmful to our computers. And on at least one type of file, the system will do nothing at all. Unfortunately (or fortunately, depending on what view you take this as), using DoCmd.SetWarnings before executing this method has no effect on these warnings (as they are prompted by the OS rather than Access).
Also, certain file types may not open with the currently registered program. For instance, a .jpg image may open in Internet Explorer rather than Microsoft Picture Viewer.
While Application.FollowHyperlink is very easy to use, and certainly does have its place in VBA, one must be aware of these workings before using it all over.
Allen Browne has come up with a function based on Application.FollowHyperlink, which includes workarounds for some of behaviors that may not be desirable when opening local or server share files. His GoHyperlink() function and documentation can be found at http://www.allenbrowne.com/func-GoHyperlink.html.
The Shell() Function
The Shell function that is built into VBA does not suffer these same security measures. To use Shell, simply pass the path of the executable and whatever command-line switches that may be applicable.
Shell """C:\....\MSACCESS.exe C:\Yourdb.mdb /Excl /x mcrStartup"""
x = Shell("""C:\....\MSACCESS.exe C:\Yourdb.mdb /Excl /x mcrStartup""")
This is handy because we do not have to worry about the files being opened by any other program other than what we specify when using Shell, and we have much greater control over the parameters with which the program is executed.
The Shell function returns a Variant (Double) representing the program's Task ID if successful, or 0 otherwise. This can be used to verify the program executed correctly.
The ShellExecute API
The ShellExecute API is a very powerful and versitile tool for handling files. Open files and folders, print documents, etc. With the ShellExecute API, we can pass the filename itself, and Windows will execute the file based on the currently registered application. For instance, instead of Shelling "C:\...\notepad.exe", we can simple pass "C:\sometext.txt" and notepad will automatically open (assuming that notepad is the registered application for the .txt file extension). The MVPS.org example of the ShellExecute API has been set up to call the Open With dialog box in the event that no registered filetype is found.
At the same time, ShellExecute can be used exactly the same way as Shell (you can pass an executable with switches to it and obtain the same results).
Document type files (.doc, .xls, .txt, .pdf, etc. etc.) can be printed directly from the ShellExecute API as well, by passing the lpOperation argument as "print" rather than "open".
Using ShellExecute is akin to working directly at a system level... no security prompts or surprises as such. Though it is a bit more difficult to get a good understanding of the ins and outs of the ShellExecute API, there are definate benifits to using it.
Return values of the API and details regarding parameters can be found at AllAPI.Mentalis.org
|This page has been accessed 25,002 times. This page was last modified 15:50, 2 March 2012 by Jack Leach. Disclaimers|