Find My Documents folder

I have a MS Access application that needs to be deployed in over 300 different locations with varying computer systems. I therefore propose using SageKey to deploy the app to avoid configuration issues with other versions of MS Access. I therefore plan on saving the FE and BE of my app in one of the recommended locations for apps.

However my app requires the user to save a csv or xls file in a specific location.. The app looks in that folder and if it finds files it imports the data and moves the imported file to an archive subfolder. I thought it best to create a subfolder in My Documents with my app name where they could easily save the csv and xls files and a subfolder of that folder to act as the archive.

How can I reliably find the location of the My Documents folder using VBA? Or is there a recommended alternative approach to this situation?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I've done things like this before and the problem is that the user may have renamed their "My Documents" folder to something else, like "My Docs" for example.  Hence, I would suggest that you create a folder in the user profile directory (of which "My Documents" is a subfolder) which you can always reliably get:

strFolder = Environ$(“USERPROFILE”) & “YourFolderNameHere”


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
From Vista and forward it is Documents:

strFile = Dir(Environ("USERPROFILE") & "\Documents\", vbNormal)

Normally users cannot rename this folder.

Luke ChungPresidentCommented:
To do this correctly and take into account the different operating systems, bitness, user permissions, languages, etc., you should use Windows API calls.

In our commercial Total Visual SourceBook code library, one of the 227 modules is for System Folders:

Hope this helps.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Rob4077Author Commented:
Thanks for all your suggestions.  

With the variability possible, might I be better off getting them to save the csv and xls files in C:\MyApp instead of Documents or My Documents? I am not sure if most systems allow users to save to main drive folders

Otherwise it's sounding like strFile = Dir(Environ("USERPROFILE") & "\Documents\", vbNormal) might be the easiest and most universally safe option
Why don't you give the user the choice of where to save the CSV.

This can be done by creating a table to store the location of the folder. You can then create a form for the user to select a folder for storing the CSV when they first use the application or if the folder previously stored in the table no long exist.

Your app can then look in that table for the folder path.
Rob4077Author Commented:
Thanks for the added suggestion. I will give some thought and discuss with the stakeholders before finalising. Thanks
For what its worth, you can open the User's profile directory quickly by start->run and typing a single dot/period/full stop.
Rob4077Author Commented:
Thanks for the tip!
Rob4077Author Commented:
Hi Gustav, if you're still linked to this question, I just tried running a debug.print Dir(Environ("USERPROFILE") & "\Documents\", vbNormal) in my code and it generates a blank
Rob4077Author Commented:
BTW ignore last post - it's an easy adjustment to get what I need.,
Gustav BrockCIOCommented:
Well, these should give you the folder itself:

? Dir(Environ("USERPROFILE") & "\Documents", vbDirectory)

? Dir(Environ("USERPROFILE") & "\Doc*", vbDirectory)

If not, this method seems not to be bullet-proof.

Rob4077Author Commented:
Hi Gustav, I trialled it on a machine that resides on a server and it doesn't work, My Documents is redirected elsewhere and I can't rely on this method to make it work. I don't know if it's fair to assume that every PC will have a C:\ but at the moment I am tending towards that. I could let them use their preferred location as per Sheils' suggestion but that assumes the users will be able to sensibly pick a location during installation. The alternative is to use Environ$(“USERPROFILE”) & “YourFolderNameHere”, which does work, but again may be a little harder to find than C:\. Many of my users are not computer savvy at all.
...but again may be a little harder to find than C:\. Many of my users are not computer savvy at all.

This is why I stated that you can open the profile folder by Start->Run->. :)

You can tell users to do this, then tell them to open the folder "YourFolderNameHere" within the window that opens.  Alternatively you could create a shortcut to this folder programatically or just simply programatically open it?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.