Category: Uncategorized

Finding the correct path to write the file to when writing export routines or other routines that write files to the active users computer has always been a problem. Normally I have ended up using a path from a user profile document or by simply using the root of the C-drive. This has served me well but the optimal solution would be using the “My Documents”-folder of the user instead. This alliviates a number of problems that might otherwise occur:

• The user may not have a C-drive
• The user may not have permissions to write to the C-drive
• The user may expect the file to go some place else than the root of the C-drive

Using the “My Documents”-folder is now the preferred way to doing it from my perspective. So how do we get at the actual path of the folder? You could use the HOMEDRIVE and HOMEPATH environment variables like this:

Dim export_path As String
export_path = Environ(|HOMEDRIVE|) + Environ(|HOMEPATH|) + "My Documents"

This will work for many users but not on my machine since I have remapped the location of the “My Documents”-folder to C:Data (the above will result in “C:Documents and Settingslekkim.HQMy Documents”). It also wouldn’t work if the users “My Documents”-folder has been mapped to a network location via Active Directory policies.

As it turns out it is a little difficult to get the location of the users “My Documents”-folder since there isn’t an environment variable that holds this piece of information. The solution is to use the SHGetFolderPath function of the Windows API.

Start by declaring the function in the Declarations section of your code. Also declare a number of constants for the “My Documents” and “My Pictures”-folders:

Declare Function SHGetFolderPath Lib "shfolder.dll" Alias "SHGetFolderPathA" (Byval hwndOwner As Long, Byval nFolder As Long, Byval hToken As Long, Byval dwReserved As Long, Byval lpszPath As String) As Long
Private Const MY_DOCUMENTS& = &H5
Private Const MY_PICTURES& = &H27
Private Const HWND_CURRENT_WINDOW& = &H0

Since the returned string will be 0-terminated as C-strings are we need a utility function to crop the returned result:

Private Function TrimNull(startstr As String) As String
   Dim i As Integer
   Dim char As String
   For i=Len(startstr) To 1 Step -1
      char = Mid$(startstr, i, 1)
      If Asc(char) = 0 Then
         TrimNull = Mid(startstr, 1, i-1)
      End If
   Next
End Function

Finally we need a utility function to make it easier to call the SHGetFolderPath function:

Private Function GetFolderPath(folder As Long) As String
   'declarations
   Dim buff As String

   'fill buffer with the specified folder item
   buff = Space$(256)
   If SHGetFolderPath(-1, folder, -1, &H27, buff) = 0    Then
      GetFolderPath = TrimNull(buff)
   End If
End Function

Putting it all together means that we can get the “My Documents” or “My Pictures” folder as easy as the following:

Dim export_path As String
export_path = GetFolderPath(MY_DOCUMENTS)
Msgbox "My Documents is at: " & export_path

export_path = GetFolderPath(MY_PICTURES)
Msgbox "My Pictures is at: " & export_path

That’s it! You can get more constants for the SHGetFolderPath function by looking up the function in a Windows API reference.