Point to My Docs in Win7/XP, Excel 03/07

[MikeZz]

Hi,
I have a file that's shared on multiple computers using different OS and
versions of Excel....
Windows 7, XP Pro, Excel 03 and Excel 07.

Is there a common function that can get the true location of the "My
Documents" folder for all the above combinations?

I guess I wouldn't need the actual folder name if there is a common alias I
could grab on to. It just has to be able to work if someone moved the "My
Documents" location to a non-default drive or folder.

Thanks!

 

[Chip Pearson]

You can usually get it from Environ variables:

Function MyDocumentsFolder() As String
MyDocumentsFolder = Environ("HomeDrive") & _
Environ("HomePath") & "\My Documents"
End Function

Or, to be safe in case those variables don't work, you can use the
GetSpecialFolders function I have at
http://www.cpearson.com/excel/SpecialFolders.aspx . On that page is a
downloadable module that has all the code you need. With that, you can
use

Function MyDocumentsFolder() As String
MyDocumentsFolder = GetSpecialFolder(CSIDL_PERSONAL)
End Function

This has the advantage that you aren't hard coding anything, which may
be important for supporting other locales and languages. Note also
that My Documents is a virtual folder, whose contents are stored in
another real folder. For example, on Windows 7, "My Documents" is
really an alias for "Documents". The environ variables give you the
"My Documents" folder while GetSpecialFolder gives you "Documents".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com