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

M

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!
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top