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
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 Settings\lekkim.HQ\My 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& = &H0Since 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 FunctionFinally 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 FunctionPutting 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_pathThat's it! You can get more constants for the SHGetFolderPath function by looking up the function in a Windows API reference.