Does This File Register with You

<< Click to Display Table of Contents >>

Navigation:  Word, Outlook, Excel and Dropbox >

Does This File Register with You

200501_ds2 Doug Steele

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:

FollowHyperlink <full path to file to open>

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

FollowHyperlink "C:\Personal\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:

FollowHyperlink "C:\Data.xls", "Sheet1!B1"

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

FollowHyperlink "C:\Present.ppt", 3

FollowHyperlink "C:\Present.ppt", "3"

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:

Private Sub cmdOpen_Click()

Dim hlkCurr As Hyperlink

  If Len(Me.txtFile) > 0 Then

    Set hlkCurr = Me.cmdOpen.Hyperlink

    With hlkCurr

      .Address = Me.txtFile

      .Follow

    End With

  End If

End Sub

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:

Me.cmdOpen.HyperlinkAddress = vbNullString

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.

Private Declare Function ShellExecute _

  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

Private Const ERROR_SUCCESS = 32&

Private Const ERROR_NO_ASSOC = 31&

Function ShellEx( _

  FileName As String, _

  Optional WindowStyle As Long _

    = vbNormalFocus _

) As Long

Const NoAssoc As String = _

  "rundll32.exe shell32.dll,OpenAs_RunDLL "

Dim lngReturn As Long

Dim varTaskID As Variant

  lngReturn = ShellExecute(hWndAccessApp, _

    vbNullString, _

    FileName, _

    vbNullString, _

    vbNullString, _

    WindowStyle)

  If lngReturn > ERROR_SUCCESS Then

    lngReturn = 0

  Else

    Select Case lngReturn

      Case ERROR_NO_ASSOC

        varTaskID = Shell(NoAssoc _

          & FileName, WIN_NORMAL)

        lngReturn = _

          IIf(varTaskID <> 0, 0, ERROR_NO_ASSOC)

      Case Else

    End Select

  End If

  ShellEx = lngReturn

End Function

You can use my function like so to open file Data.xls:

Call ShellEx("C:\Data.xls")

If you want the application to open maximized (and with focus), you'd use this code:

Call ShellEx("C:\Data.xls", vbMaximizedFocus)

If you want to check the return code, you'll need to code something like this:

lngReturn = ShellEx("C:\Data.xls")

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.

200501_ds1
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:

"C:\AcroRd32.60\Reader\AcroRd32.exe" /p /h "%1"

200501_ds2
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:

Declare Function RegOpenKeyEx _

  Lib "advapi32.dll" _

  Alias "RegOpenKeyExA" ( _

  ByVal hKey As Long, _

  ByVal lpSubstrKeyName As String, _

  ByVal ulOptions As Long, _

  ByVal samDesired As Long, _

  phkResult As Long _

) As Long

Declare Function RegQueryValueEx _

  Lib "advapi32.dll" _

  Alias "RegQueryValueExA" ( _

  ByVal hKey As Long, _

  ByVal lpszValueName As String, _

  ByVal lpdwReserved As Long, _

  lpdwType As Long, _

  lpData As Any, _

  lpcbData As Long _

) As Long

Declare Function RegCloseKey _

  Lib "advapi32.dll" ( _

  ByVal hKey As Long _

) As Long

You'll also need to define the following constants:

Public Const HKEY_CURRENT_USER = &H80000001

Public Const HKEY_LOCAL_MACHINE = &H80000002

Public Const HKEY_USERS = &H80000003

Public Const HKEY_DYN_DATA = &H80000006

Public Const HKEY_CURRENT_CONFIG = &H80000005

Public Const HKEY_CLASSES_ROOT = &H80000000

Public Const ERROR_SUCCESS = 0&

Public Const REG_SZ = 1

Public Const REG_BINARY = 3

Public Const REG_DWORD = 4

Public Const KEY_QUERY_VALUE = &H1

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:

lngResult = ReadRegistry(HKEY_CURRENT_USER, _

  ".pdf", "", "", strValue)

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

lngResult = ReadRegistry(HKEY_CURRENT_USER, _

  " AcroExch.Document/shell/Print/command ", _

  "", "", strValue)

Here's the full version of my function:

Public Function ReadRegistry( _

    ByVal TopLevelKey As Long, _

    ByVal SubKeyName As String, _

    ByVal ValueName As String, _

    ByVal Default As Variant, _

    Value As Variant _

) As Long

Dim booValue As Boolean

Dim lngError As Long

Dim lngHandle As Long

Dim lngLenData As Long

Dim lngResult As Long

Dim lngValue As Long

Dim lngValueType As Long

Dim strValue As String

  lngResult = -1

  Value = Default

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:

  lngResult = RegOpenKeyEx(TopLevelKey, _

    SubKeyName, 0, KEY_QUERY_VALUE, lngHandle)

  If lngResult <> ERROR_SUCCESS Then

    ReadRegistry = lngResult

    Exit Function

  End If

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.

  lngResult = RegQueryValueEx(lngHandle, _

    ValueName, 0&, lngValueType, _

    ByVal 0&, lngLenData)

  If lngResult <> ERROR_SUCCESS Then

    ReadRegistry = lngResult

    Exit Function

  End If

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

  Select Case lngValueType

    Case REG_SZ, REG_EXPAND_SZ

      strValue = Space(lngLenData)

      lngResult = RegQueryValueEx(lngHandle, _

        ValueName, 0, REG_SZ, _

        ByVal strValue, lngLenData)

      If lngResult = ERROR_SUCCESS Then

        Value = Left$(strValue, lngLenData - 1)

      Else

        ReadRegistry = lngResult

        Exit Function

      End If

    Case REG_BINARY

      lngLenData = Len(booValue)

      lngResult = RegQueryValueEx(lngHandle, _

         ValueName, 0, REG_BINARY, _

         booValue, lngLenData)

      If lngResult = ERROR_SUCCESS Then

        Value = booValue

      Else

        ReadRegistry = lngResult

        Exit Function

      End If

    Case REG_DWORD

      lngLenData = 32

      lngResult = RegQueryValueEx(lngHandle, _

        ValueName, 0, REG_DWORD, _

        lngValue, lngLenData)

      If lngResult = ERROR_SUCCESS Then

        Value = lngValue

      Else

        ReadRegistry = lngResult

        Exit Function

      End If

  End Select

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

  lngResult = RegCloseKey(lngHandle)

  ReadRegistry = lngResult

End Function

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:

Function PrintCommand( _

  FileExtension As String _

) As Long

Dim lngReturn As Long

Dim strExtension As String

Dim strProgId As String

Dim strProgram As String

Dim strRegistryKeys As String

Dim strReturn As String

  strRegistryKeys = ""

  If Len(FileExtension) > 0 Then

    If Left$(FileExtension, 1) = "." Then

      strExtension = FileExtension

    Else

      strExtension = "." & FileExtension

    End If

    lngReturn = ReadRegistryValue( _

      HKEY_CLASSES_ROOT, _

      strExtension, "", "", strProgId)

    If lngReturn = ERROR_SUCCESS Then

      strReturn = FileExtension & _

        " is associated with ProgId " & _

        strProgId & vbCrLf

      lngReturn = ReadRegistryValue( _

        HKEY_CLASSES_ROOT, _

        strProgId & "\shell\print\command", _

        "", "", strProgram)

      If lngReturn = ERROR_SUCCESS Then

        strReturn = strReturn & _

          "The print command is " & strProgram

      Else

        strReturn = strReturn & _

          "No print command is defined."

      End If

    Else

      strReturn = FileExtension & _

        " FAILURE: lngReturn = " & lngReturn

    End If

  Else

    strReturn = "Please enter an extension"

  End If

  PrintCommand = strReturn

End Sub

Here's what happens when you run that code:

?PrintCommand("")

Please enter an extension

?PrintCommand(".pdf")

.pdf is associated with ProgId AcroExch.Document

The print command is

   "C:\AcroRd32.60\Reader\AcroRd32.exe" /p /h "%1"

?PrintCommand(".txt")

.txt is associated with ProgId txtfile

The print command is

    %SystemRoot%\system32\NOTEPAD.EXE /p %1

?PrintCommand(".doc")

.doc is associated with ProgId Word.Document.8

The print command is

      "C:\MSOff32.972\Office\Winword.exe" /x

?PrintCommand(".cur")

.cur is associated with ProgId curfile

No print command is defined.

?PrintCommand(".xxx")

.xxx FAILURE: lngReturn = 2:

   The system cannot find the file specified.

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.

 

Your download file is called 501STEELE.ZIP in the file SA2005-01down.zip

This can be purchased with all the other downloads on this page