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.
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"
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.
