VBA Special folders Documents/My Documents and multi locale language issue

[JeremyXL2]

Hello,

I am new here so if I slip up on posting protocols then please give me a nudge in the right direction.

What I need to do is:- programmatically provide access to the current user's Documents/My Documents folder in an Excel/VBA app.
The app will have only a few users but situated in different Locales e.g. Italy, Germany, Spain, Iceland.
I have no previous experience in trans-locale work. The texts in the app itself will be English language only but the code will need to accomodate certain technicalities.
I am trying to avoid code situations where these might arise but this one seems unavoidable - I think.
This is non-commercial pro-bono work for non-institution academic use (i.e. no money for anything - all for the pursuit of knowledge) so I am trying to keep my workload to a minimum
There are no resources for locale related testing prior to initial deployment. My aim is to keep the pain of user based beta testing to a minimum.

My understanding is that, when non-English locale settings are applied, the Documents/My Documents special folders are often actually named in the Locale language.
Because of this the use of literals to reference special folders is doomed to failure.
Following a bit of searching the web - my understanding is that the normal approach should suffice i.e. to obtain the special folder name from the system using Environ("MyDocuments").
Thread 678937 deals with this in part but pre-dates Windows 10 and and does not cover the locale issue.

The next part harks back to earlier Windows OS version changes - i.e is it 'Documents' or 'Mydocuments" - not everybody has migrated to Windows 10 and the reality is that, with this user base at least, some are still using XP.

Previously, rather than retrieving the OS version, I get over this with:- (sorry not sure how this code tagging works so avoided it for now - tag the block or tag each line?)

Set WshShell = CreateObject("WScript.Shell")

sMyDocsPath = WshShell.SpecialFolders("Documents")

If sMyDocsPath = "" Then '"Documents" not found as an environment variable

sMyDocsPath = WshShell.SpecialFolders("MyDocuments")

End If

If sMyDocsPath = "" Then Goto ERROR_HANDLER


'otherwise OK to use sMyDocsPath

The error handler gets the user to browse to the correct or desired location - I could go straight to this but most users don't want to be bothered with or struggle with it.

Usually sMyDocsLocaleName will then contain the full path to the user's Documents/My Documents folder and, hopefully, with the Locale language dependent elements taken care of.

So my specific questions:

1 - Am I correct in my understanding that Environ("Documents") and/or Environ("MyDocuments") returns the path with any locale language differences accommodated?

2 - Is my workaround code for the Windows OS version differences an adequately robust solution?

If any of you are aware of other 'variable locale' related pitfalls then any tips would be greatly appreciated.

Many thanks in advance - Jeremy

 

[mole999]

have a look at > MS Excel: How to use the ENVIRON Function (VBA) there is a way to list the environs, try for 30 and that should give you common paths

 

[JeremyXL2]

Thanks mole999,

As with you, as with me - a means to an end and oh so many of them.

As I understand it the parameter name in Environ() is always in English regardless of locale so I have no real trouble with that. I agree that passing the index is simpler than worrying about whether to pass "Documents" or "Mydocuments" and it is language independent in any case. That said I would not entirely trust Microsoft to not change either in the future - long experience of encountering the word "deprecated" leaves a lasting wariness.

My main concern is whether or not the returned path is always in English or tracks the Locale language e.g. Windows 7 in Germany "C:\Users\<Username>\Eigene Dokumente" . And even then I am not sure whether 'Users' remains in English. The only references I have found online merely hint at it rather than making explicit statements. Worst case I am going to have to set a machine up with different locale settings and try it.

Thanks for code tagging example - If I have more that 1 line of code do I wrap each line or the from start of the first line to the end of the last line?

That Excel Translate tool could be quite handy. Trying to avoid worksheet functions for this app but things like Transpose () for array manipulation are useful even in a VBA only situation

 

[mole999]

code tagging, when you type your message look to the message box top and see <vba/> select your code and press that and it will all be done for you

 

[JeremyXL2]

Thanks again Mole999. I just spotted the Test forum - guess I will go have a few practice runs over there.

 

[mole999]

Thanks again Mole999. I just spotted the Test forum - guess I will go have a few practice runs over there.

best place for it, environ has been around a long time so worth coding for, with fingers crossed rather than trying multiple methods based on language. Different languages use , or . so that has to be noted and sorted for though i have never had to do that