How to retrieve some commonly used folder paths using contemporary syntax.
Access Archon #148
Introduction
Back in 1996 or 1997 I wrote a set of articles for Office Underground Office Newsletter (that was the one printed on bright yellow paper) on retrieving various paths, such as the Windows and Office folder paths. Before Windows 95, this was a fearfully technical chore, involving calls to the Windows API with references to profile files such as WIN.INI or WINWORD6.INI. For example, to get the Word Documents folder path, you had to put the following declaration in a module’s Declarations section:
Declare Function GetPrivateProfileString Lib “Kernel” (ByVal SName$, ByVal KName$, ByVal Def$, ByVal ret$, ByVal size%, ByVal Fname$) As Integer
Then, in a procedure,
‘Get Word documents path from WINWORD6.INI
strIniFileName = “WINWORD6.INI”
strIniSection = “Microsoft Word”
strIniKey = “DOC-PATH”
‘Initialize return string.
ret$ = String$(255, Chr(32))
‘Call Windows Kernel DLL.
intReturn = GetPrivateProfileString(strIniSection, strIniKey, “”, ret$, Len(ret$), strIniFileName)
strDocsPath = Left(ret$, 150)
strDocsPath = Left$(strDocsPath , InStr(strDocsPath , Chr$(0)) – 1) & “”
Modern Path Retrieval
Fortunately, you don’t have to go through all that now – this article will show you how to retrieve some commonly used folder paths using more contemporary syntax. Some of the paths can be conveniently retrieved from the Word PrivateProfileString property, and others from the Windows Registry. The Access path can be retrieved directly using the Access SysCmd method.
Import the saved module basPathInfo.bas into an Access database, and set a reference to the Word object model. You can use the functions in the module anywhere in your VBA code, to get a path that you need. For example, if you need to import data from a file located in the same folder as the Access database, you could set a variable equal to the CurrentPath () function:
strCurrentPath = CurrentPath()
Or to set a reference to a Word template in a “Personal Docs” folder under the Word User Templates folder, use this line:
strTemplatesPath = UserTemplatesPath() & “Personal Docs”
VBA Code for Retrieving Folder Paths
Option Compare Database
Option Explicit
‘Declare public Word Application variable for use in procedures
Public pappWord As Word.Application
Public Function DocsPath() As String
‘This is the My Documents path
On Error GoTo ErrorHandler
Set pappWord = GetObject(, “Word.Application”)
‘The following line should return the Docs path, but it actually returns
‘the current path most of the time, so it is not reliable
‘DocsPath = pappWord.Options.DefaultFilePath(wdDocumentsPath) & “”
DocsPath = pappWord.System.PrivateProfileString(“”, _
“HKEY_CURRENT_USERSOFTWAREMicrosoftWindowsCurrentVersion
ExplorerShell Folders”, _
“Personal”) & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set pappWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function CurrentPath() As String
‘This is the path where the Access database is located
On Error GoTo ErrorHandler
CurrentPath = Application.CurrentProject.Path & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Function
Public Function UserTemplatePath() As String
‘This is the Word User Templates path where your personal Word
‘templates are stored
On Error GoTo ErrorHandler
Set pappWord = GetObject(, “Word.Application”)
UserTemplatePath = _
pappWord.Options.DefaultFilePath(wdUserTemplatesPath) & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set pappWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function WinPath() As String
‘This is the Windows path
On Error GoTo ErrorHandler
Set pappWord = GetObject(, “Word.Application”)
WinPath = pappWord.System.PrivateProfileString(“”, _
“HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersion”, _
“PathName”) & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set pappWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function OfficePath() As String
‘This is the Office path
On Error GoTo ErrorHandler
Set pappWord = GetObject(, “Word.Application”)
OfficePath = pappWord.Options.DefaultFilePath(wdProgramPath) & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set pappWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function WorkgroupTemplatePath() As String
‘This is the Word Workgroup Templates path where organizational Word
‘templates are stored
On Error GoTo ErrorHandler
Set pappWord = GetObject(, “Word.Application”)
WorkgroupTemplatePath = _
pappWord.Options.DefaultFilePath(wdWorkgroupTemplatesPath) & “”
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set pappWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
References
The code in the sample database needs the following reference (in addition to the default references):
Microsoft Word 11.0 Object Library
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of my Web site. It is accarch148.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
basPathInfo.bas |
Saved VBA module |
Import into any Access database |
