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.
Thanks a bunch. I have been finding myself wanting to get just this information lately, but have been too busy to figure it out on my own.
Sean—
LikeLike
Well, I wish I had found this about 2 days ago! I found my own C API calls to do the same thing, and then before blogging it I found your post!
So, I blogged my way and I also mention this post as well. you can check it out here:
http://www.lotusgeek.com/sapphireoak/lotusgeekblog.nsf/d6plinks/ROLR-6SXKYP
Good stuff, Mikkel!
–Rock
LikeLike
If you use the Windows Scripting Host, which ships with Win2k and XP, it’s much simpler and doesn’t require any API calls:
Dim sDesktop As String
Dim sMyDocs As String
Dim oShell As Variant
Dim oFS As Variant
Set oShell = CreateObject(“WScript.Shell”)
Set oFS = CreateObject(“Scripting.FileSystemObject”)
sDesktop = oShell.SpecialFolders(“Desktop”)
sMyDocs = oShell.SpecialFolders(“MyDocuments”)
LikeLike
That is FRIGGIN KEWL Charles. I need to explore what else is available for the scripting host. Where can I get some more info about it?
(Googling now)
I am also going to post an update, with proper attribution to you, on this technique.
Great job, thanks!
LikeLike
Rock,
there’s a lot of nice things in the Windows scripting API – one thing I use quite a lot is the regular expression capabilities it affords you from LotusScript (post). An added benefit is that it is available on any Windows machine so no installation of additional components is required.
LikeLike