Does This File Register with You

Doug Steele looks at how to interact with external files–specifically, how to open or print them.

If I have the name of a file, how can I open it in whatever program is appropriate for it? In other words, if I have a Word document, how can I open it in Word, or a spreadsheet in Excel?

It’s fairly straightforward to open each of the files in the appropriate program, provided the file extension has been explicitly associated with a program. Access provides a Hyperlink object that can be used to do this, or you can use the application’s FollowHyperlink method. In fact, using either of these approaches, it’s even possible to open the document at a specific place.

In my opinion, the easiest approach is to use the FollowHyperlink method.

If you check in Help, you’ll find a number of parameters that can be used with this method, but most of them only apply when you’re dealing with Web pages. For files, really all that apply are the first two parameters (named Address and Subaddress), where Address is the name of the file to be opened, and Subaddress is the (optional) named location in the document specified in Address. What’s required for Subaddress varies by document type, and, in fact, Subaddress cannot be used with all documents.

Using the method is simplicity itself:

To show it in context, this code opens the file MyResume.doc:

To be absolutely correct, my last example should have used Application.FollowHyperlink, but it works fine without the Application keyword.

If you want to play with the Subaddress feature, I know that the combinations listed in Table 1 will work.

Table 1. Valid Subaddresses.

Type of file Subaddress
Word Name of a bookmark in the file
Excel Reference to a specific cell or range
PowerPoint Slide number

As an example of using Subaddress, you can open your Excel spreadsheet to cell B1 on Sheet2 using this:

You can open your PowerPoint presentation to the third page using either of these two lines of code:

Alternatively, you can use the Hyperlink property associated with such controls as command buttons, image controls, and labels. One way is to use code like the following, associated with a command button on a form, intended to open whatever file is contained in a textbox on the form:

For reasons I didn’t have time to fully explore, I found that using the last method required me to reset the HyperlinkAddress for the command button, or else the document would open twice. So I always add an additional line of code after successfully opening the document:

Finally, in keeping with my tradition of doing things the hard way, you can also use the ShellExecute API to open a document. I’ve added logic to invoke the familiar Open With dialog that lets the user pick what program to use if Windows doesn’t know what program is associated with the file.

What if rather than open the file, I want to print it? I have a table structure where people can “attach” various documents to their record. Really the table just stores the directory of the file they’ve attached. I’d like to run a loop through the file paths and print out each file.

As I implied earlier, the information about which program to use with which file extension is stored in the Windows Registry, and the API call or Hyperlink call takes advantage of that. If you right-click on a file in Windows Explorer, you should notice that many files give you an option of printing.

If Windows can figure out how to do this, surely you can too! Fortunately, it turns out that it’s not all that difficult to do, although it does require spelunking in the Registry. Don’t worry: We’re only going to read information from the Registry, not write to it, so there’s little chance of causing problems. The first section of the Registry (HKEY_CLASSES_ROOT, usually abbreviated to HKCR) is where file association information is stored. Look for an entry corresponding to the file extension of the file that you want to print in that section, and the default key for the extension should correspond to the file type associated with that file extension. In Figure 1, you can see that the .pdf extension corresponds to file type AcroExch.Document.


Figure 1

Once you know the file type (also referred to as ProgId), you can look that up in the HKCR section of the Registry as well. One of the subkeys for that entry should be Shell and, under the Shell subkey, there should be entries corresponding to the various options that appear when you right-click on the file in Windows Explorer. In Figure 2, you’ll see that there are three separate options under Shell: Open, Print, and Printto. (The reason why Printto doesn’t appear as an option on the context menu of a PDF file is that there’s no Default value set for the Printto entry.) Look in the Print entry, and there should be a Command entry that shows the actual command used to print that file type. Figure 2 has the following command:

 


Figure 2

If you replace %1 with the name of the file to print and use the Shell command to execute that, you can print the file.

So how can you retrieve all that information from the Registry yourself?

You guessed it: You need API calls. It’s not my intent to provide all of the code required to interact with the Registry. For instance, while there are calls that allow you to enumerate all of the subkeys under a given key in the Registry, I’m not going to show them here. My excuse is that the exact keys that I need are known in this case. I’m also not going to show code that allows you to write to the Registry.

The critical declarations are:

Once you’ve got all of that taken care of, you can create the following function to actually read a value. The function will return zero if it’s successful, or a non-zero value (representing the error encountered) if not. You must pass four values to the function:

  • TopLevelKey–An indicator of the top-level Registry key. This is what the following constants (previously defined) are used for:
  • HKEY_CURRENT _USER
  • HKEY_LOCAL_MACHINE
  • HKEY_USERS
  • HKEY_DYN_DATA
  • HKEY_CURRENT_CONFIG
  • HKEY_CLASSES_ROOT
  • SubKeyName–The name of the subkey in the Registry.
  • ValueName–The name of the actual entry.
  • Default–The default value to return if one isn’t found.

In Figure 1, the SubKeyName is “.pdf” and the ValueName is “” (Default corresponds to a zero-length string); in Figure 2, the SubKeyName is AcroExch.Document/shell/Print/command (ValueName would still be “”).

In addition to returning a flag that indicates success or failure, the function also populates the field Value with what’s stored in the Registry for that subkey and value. In Figure 1, Value is AcroExch.Document, whereas in Figure 2, it’s “C:\AcroRd32.60\Reader\AcroRd32.exe” /p /h “%1”.

In other words, to retrieve the value in Figure 1, you’d use this code:

To retrieve the value in Figure 2, you’d use this code:

Here’s the full version of my function:

The first thing that’s necessary is to open the key in the Registry. The call to RegOpenKeyEx will return ERROR_SUCCESS (0) if the key exists, or a non-zero value if there’s an error. If the key can be opened, the call will also return a handle to the Registry entry that you’ll use when retrieving the value:

If the key exists, a first call to RegQueryValueEx will determine the type of value that will be returned, as well as tell how large a buffer will be required to retrieve the value.

 

Once you know the type of value being returned, a second call to RegQueryValueEx allows you to retrieve the value:

 

Once the value has been read, a call to RegCloseKey will release the handle, and you’re done:

Now that you’ve got a function to read Registry values, it becomes fairly simple to read the Registry to determine how to print a given file. The following function will return 0 if it finds a print command to use (and will return that print command as CommandToUse), or a non-zero value otherwise:

Now, I have a confession to make. I was intending to continue with code to show how you could take this information you extracted from the Registry and use the Shell command to use it to actually print the file. However, it turns out that it isn’t nearly as neat as all that, so I’m unable to present a completed solution to you.

If you look at what was returned for the .pdf extension, it works very well. Simply use the Replace function to replace %1 with the name of the file that you want to print, pass that revised string to Shell, and the PDF file prints.

Look, though, at what was returned for the .txt extension. Not only do you need to replace %1 with the name of the text file, but you also need to replace %SystemRoot% with the value of the Environment Variable SystemRoot. That’s not bad: Environ(“SystemRoot”) will give you that value, so you simply need to parse what’s returned by the function to determine what environment variables are there. You do have some warning: The first call to RegQueryValueEx in the ReadRegistry function actually returns REG_EXPAND_SZ, not REG_SZ, so it’s actually quite simple to know when you need to do that parsing.

However, look at what was returned for the .doc extension. There’s nothing in that string to indicate how to pass a specific file to Word to have it print.

Unfortunately, the publishing deadline has prevented me from delving deeper into this matter, and I’m going to have to leave the issue as incomplete. Hopefully you’ve found it useful to learn how to untangle the file extension/application linkage in the Registry.

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in Office. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.