VBA / VBS to get Desktop folder string

[John Keith]

'*** Save this as GDF.VBS and running it returns the user desktop folder
Option Explicit

msgbox GetDesktopFolder

Function GetDesktopFolder()
Dim objShell
Set objShell = CreateObject("WScript.Shell")
GetDesktopFolder = objShell.SpecialFolders("Desktop")
Set objShell = nothing
End Function
--

Put that same Function in to an Access VBA module and it returns the AllUser
desktop folder...

I note that the WScript SpecialFolders also accepts "AllUsersDesktop"; so
why does just "Desktop" return the wrong one?

Regards,
John

 

[Dirk Goldgar]

'*** Save this as GDF.VBS and running it returns the user desktop folder
Option Explicit

msgbox GetDesktopFolder

Function GetDesktopFolder()
Dim objShell
Set objShell = CreateObject("WScript.Shell")
GetDesktopFolder = objShell.SpecialFolders("Desktop")
Set objShell = nothing
End Function
--

Put that same Function in to an Access VBA module and it returns the
AllUser
desktop folder...

I note that the WScript SpecialFolders also accepts "AllUsersDesktop"; so
why does just "Desktop" return the wrong one?

It doesn't do that for me. When I run the code in Access 2003 under Vista,
I get my own desktop folder, not the Public desktop.

I'd prefer to use the API code here anyway:

http://www.mvps.org/access/api/api0054.htm
API: Retrieving a Special Folder's location

.... though I have to admit I don't understand the difference between the
constants CSIDL_DESKTOP and CSIDL_DESKTOPDIRECTORY. On my system, they both
return the same thing.

 

[John Keith]

Thanks for the reply:

I have used several of Dev's solutions before but that one has to be the
most verbose! and as usual it works.

CSIDL_DESKTOP and CSIDL_DESKTOPDIRECTORY both return the same folder string
on XP/Access2003 for me too.

But for my prefered more simple version in Access2003, I discovered it seems
to choose the AllUsersDesktop result no matter what is used as the parameter.
I tried swapping the call order too incase some how an instance of the call
was being retained and not replacing the value before returning the result.

Function GetSpcFolder(ByVal sFolderType As String) As String
Dim objShell
Set objShell = CreateObject("WScript.Shell")
GetSpcFolder = objShell.SpecialFolders(sFolderType)
Set objShell = Nothing
End Function
Sub Test1()
' "Desktop" returns the same as allusersdesktop
MsgBox GetSpcFolder("myDocuments") & vbCrLf & _
GetSpcFolder("Desktop") & vbCrLf & _
GetSpcFolder("AllUsersDesktop") & vbCrLf & _
GetSpcFolder("Fonts")
End Sub

MsgBox results:
C:\Documents and Settings\All Users\Desktop
C:\Documents and Settings\All Users\Desktop
C:\Documents and Settings\All Users\Desktop
C:\Documents and Settings\All Users\Desktop

Perhaps there is a reference I need to change to make the non-API solution
work for XP and Access2003. Weird that it works fine as a VBS call but not
from VBA (on my system) I will have some others in my shop try and will
report back some results on Monday.

 

[Douglas J. Steele]

Thanks for the reply:

I have used several of Dev's solutions before but that one has to be the
most verbose! and as usual it works.

It's only verbose because there are a lot of constants defined, and a lot of
comments associated with each constant. The code for the
fGetSpecialFolderLocation function is only 12 lines.

Perhaps there is a reference I need to change to make the non-API solution
work for XP and Access2003. Weird that it works fine as a VBS call but
not
from VBA (on my system) I will have some others in my shop try and will
report back some results on Monday.

You're using Late Binding: no reference should be required. FWIW, it works
fine on my machine as well as on Dirk's.

 

[Steve Sanford]

And another simple way is to use the "Environ()" function.
If you open a DOS window and type the command SET, you will see a list of
enviroment variables. One of those is the variable "ALLUSERSPROFILE".
Put the following example code in a standard module and run it:

Public Sub dos()

MsgBox Environ("ALLUSERSPROFILE")

MsgBox Environ("ALLUSERSPROFILE") & "\DeskTop"

End Sub

My 2 pennies worth......

 

[David W. Fenton]

And another simple way is to use the "Environ()" function.
If you open a DOS window and type the command SET, you will see a
list of enviroment variables. One of those is the variable
"ALLUSERSPROFILE". Put the following example code in a standard
module and run it:

Public Sub dos()

MsgBox Environ("ALLUSERSPROFILE")

MsgBox Environ("ALLUSERSPROFILE") & "\DeskTop"

End Sub

It's not a good idea to depend on environment variables, as those
can be altered easily to something invalid.

 

[dymondjack]

It's not a good idea to depend on environment variables, as those

can be altered easily to something invalid.

I can certainly agree that most of the environ vars shouldn't be relied
upon, but I've always considered the "core" ones to be stable (USERNAME,
COMPUTERNAME, WINDIR, TEMP, TMP etc.)

I don't post this as an argument, but rather as a question. I've been of
the impression that windows and just about every program you install uses
these 'core' variables, and therefore assumed that changing them would result
in serious errors throughout the system. Obviously, I've never tried
changing WINDIR to see what happens

Unfortunately, I know little about how windows and other programs stores
information such as this. I see these vars used numberous times in various
batch files (both system created and user created).

Are the actual values that the OS and its apps use stored in the registry as
opposed to envrion vars? In which case, I should be going to the reg for the
values (or wherever they may be stored...)

If anyone could clarify, that would be great. I've always wondered about
this.

For the record, I've gone through these environ vars on Win2000, XP and
Vista (gotta love consistancy with OS versions in the workplace) and have
found that the var names that I do use are consistent between these three OS
versions.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Douglas J. Steele]

While you cannot change environment variables such as USERNAME and
COMPUTERNAME permanently without going through the correct interfaces, you
can change them for the duration of a DOS session.

Try the following.

Open a database, go to the Immediate Window (Ctrl-G), type

?Environ("USERNAME")

and hit Enter. You should see your user name.

Now, open a DOS box (command prompt, or whatever you want to call it). Open
your database using a command-line. It'll be something like:

"C:\Program Files\Microsoft Office.11\OFFICE11\msaccess.exe"
"D:\Folder\Test.mdb"

Again, go to the Immediate Window and check what the user name is. It should
be the same as the previous step.

Finally, within the DOS box, first type

SET USERNAME=XXXX

and then open your database using the command line as before (in the same
DOS box)

This time, when you go to the Immediate Window and check the user name,
you'll find it's XXXX

 

[David W. Fenton]

I can certainly agree that most of the environ vars shouldn't be
relied upon, but I've always considered the "core" ones to be
stable (USERNAME, COMPUTERNAME, WINDIR, TEMP, TMP etc.)

Given that there's a completely reliable method for getting that
information, why run the risk that some other program running in the
same environment as your Access app has changed the environment
variables? All you have to do to see how easy it is to change is to
go to the properties of My Computer, click the ADVANCED tab, click
on the ENVIRONMENT VARIABLES and then add in a new value for any
environment variable you like. This will override whatever had
previously been the value. If you can do this in the user interface,
your users can, and so can applications.

Now, I don't know why anyone *would* do those things, but the point
of writing an application is to make it bulletproof wherever
possible, not to gamble on someone not doing something that you know
they could do but can't forecast *why* they'd do it. The Windows API
calls are bulletproof and I don't see any reason why one would not
choose to use those. It's not like once you've imported Dev's code
into your app that you have to recode it every time you use it --
it's a one-time coding project, and from then on, it's no harder
than using Environ().

I don't post this as an argument, but rather as a question. I've
been of the impression that windows and just about every program
you install uses these 'core' variables, and therefore assumed
that changing them would result in serious errors throughout the
system. Obviously, I've never tried changing WINDIR to see what
happens

I have always seen environment variables as legacy values, there to
support older applications that might need them. Of course, there
are also lots of newer applications that set environment variables,
so they are obviously depending on them at some level.

But just because other developers jump off the cliff...

Unfortunately, I know little about how windows and other programs
stores information such as this. I see these vars used numberous
times in various batch files (both system created and user
created).

That's because batch files don't have access to Windows APIs, and
*have* to depend on them. The batch files run on the assumption that
the environment variables are correct at the time the command prompt
session they run in was created. If they are incorrect, then the
batch files will break, and there's no way for a batch file to fix
that.

Are the actual values that the OS and its apps use stored in the
registry as opposed to envrion vars? In which case, I should be
going to the reg for the values (or wherever they may be
stored...)

Certain values for which environment variables are created are going
to be stored in the registry, but using the registry would be
bypassing the Windows API set up for just this purpose, so I don't
know why in the world you'd do that.

It may also be that certain values are *not* stored in the registry,
so that might be an incomplete solution, anyway.

Again, there's a Windows API defined for this purpose and it's
usable in Access. There seems to me to be no justification
whatsoever for not using that.

If anyone could clarify, that would be great. I've always
wondered about this.

For the record, I've gone through these environ vars on Win2000,
XP and Vista (gotta love consistancy with OS versions in the
workplace) and have found that the var names that I do use are
consistent between these three OS versions.

Who cares? The point is that the Windows API is a better way to get
the information, and given that Access can use API calls, there's no
reason not to use the most reliable method for getting that
information.

 

[dymondjack]

Thank you, O Great One, for the reply.

Given that there's a completely reliable method for getting that
information

How I am supposed to know how reliable the API is, when I'm obviously too
stupid to know that I shouldn't be using enviroment variables?

From Dev's code:
' 4.00 All Microsoft® Windows® 95/Windows NT® 4.0.
' 4.70 All Microsoft® Internet Explorer 3.x.
' 4.71 All Microsoft® Internet Explorer 4.0
' 4.72 All Microsoft® Internet Explorer 4.01 and
Windows® 98
' 5.00 Shlwapi.dll Microsoft® Internet Explorer 5
' 5.00 Shell32.dll Microsoft® Windows® 2000.
' 5.80 Comctl32.dll Microsoft® Internet Explorer 5
' 5.81 Comctl32.dll Microsoft® Windows 2000

I see no references to Windows XP, Vista, nor the latest versions of
Internet Explorer (we are on 7 now, not 5), nor do I see any datestamp on the
webpage that may indicate when this API was current as of.

The Windows API calls are bulletproof

Are you saying that everyone should assume that every API written throughout
the evolution of the windows system is to remain reliable for all time
without question? If you could be so kind as to extrapolate on the subject,
because apparently I am the only poor fool on the face of this earth that
does not know how Windows maintains APIs.

Of course, there
are also lots of newer applications that set environment variables,
so they are obviously depending on them at some level.

Again, I must be the only person that was not aware of the fact that the
programmers who design these applications make use of environment variables
for no reason other than because they are imbeciles and have idea of what
they are doing. How presumptuous of me to assume otherwise...

using the registry would be
bypassing the Windows API set up for just this purpose, so I don't
know why in the world you'd do that.

I don't suppose it would have anything to do with the fact that I know
little of how API functions are written in relation to Windows Registry
values. Obviously, I should put my blind faith on the fact that every API
written will always be more secure than the Windows Registry, even if the API
was created years before my current operating system was released (along with
a compatible registry... imagine that).

Who cares?

Well, for some reason I had assumed that I might not have been the only
person to read this post and wondered about another person's experience with
environment variables across platforms. Sometimes I forget that I am the
only person here that does not know everything.

I have always seen environment variables as legacy values, there to
support older applications that might need them.

Who cares?

It's not like once you've imported Dev's code
into your app that you have to recode it every time you use it --
it's a one-time coding project

Thank you for verifying this... I was under the assumption that I needed to
put this into a separate module for every time I wanted to call the function.
With your obvious God-like knowledge, I should have known that you would be
able to save me many many modules of code. Is it permittable to delete those
lines in green with the little apostrophe thingies at the beginning also?





I come here, like many others, to gain (and make an attempt to spread)
knowledge and awareness. And, like many others, I would prefer not to be
treated as ignorant child due to the questions I ask. Anyone who takes
everything at face value without asking questions in doubt is bound to suffer
severe consequences at some point or another, and it is my nature to not want
to be a part of this unfortunate group of people.

Right, wrong, or indifferent, perhaps you be just a teeeeeny bit less
conceded about it?


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[dymondjack]

And, just in case you missed it, please refer to the first two words in my
previous post

Thank you

Because I did learn something, regardless of how much belittling I had to
put up with to gain the knowledge.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[John Keith]

The API method is probably the best one to go with, but I am concerned that
if the SpecialFolders method of WScript is broken (in my shop's environment),
it makes me question other WScript methods from VBA.

I have tried the three methods (API, VBS-WScript, VBA-WScript) on other
machines in my shop and all of them have the same results: failure using the
VBA-WScript, and success using the API & VBS-WScript methods.

I have tested on PCs that have XP SP3. My PC's Office application is fully
updated w/ Access 2003 (11.8166.8221) SP3 w/Access VBA version 6.05. The
other PCs I have tested this on also have XP SP3 but are on the initial
install of Office2003.

According to my tech support guru, our shop uses novel servers so no active
directory service is set up. This was the only difference that he could think
of that might cause an oddity, since it is a shop-wide issue. I'm waiting
for them to try the VBS and VBA functions in that environment (a test
platform that has the active directory set-up) to see if the WScript function
performs differently. I'll post the results of that test once it has been
tried.

Any other thoughts on settings/versions (or lack thereof) that could break
WScript calls from Access?

 

[John Keith]

Found a person running WindowsNTwith Access2000. Same broken results with
the Access call using WScript.

Starting to think that Novel servers may infact have some issue with
WScript. (still waiting on the active directory test)...

 

[John Keith]

Thanks for posting an alternate "simple" way.
Its always good to know the alternate ways to skin-that-cat.

 

[John Keith]

Found a person running WindowsXP with Access2007. Same broken results with
the Access call using WScript. (still waiting on the active directory
test)...

 

[David W. Fenton]

=?Utf-8?B?ZHltb25kamFjaw==?= <dymondjack at hot mail dot com> wrote
in

[quoting me, unattributed:]

Are you saying that everyone should assume that every API written
throughout the evolution of the windows system is to remain
reliable for all time without question?

Yes. Microsoft has made a point of maintaining full backward
compatibility for the Win16 and Win32 APIs. Since Access is a 32-bit
app, and Win32 has not changed since it was introduced in the early
90s (the first version of NT was the first OS to be based on it, and
Win95 was the first mainstream OS to include it by default; there
was a Win32 subsystem that could be installed in Win3.x, but it was
very seldom used -- I only encountered it because it was required
for a statistics package, SPSS).

If you could be so kind as to extrapolate on the subject,
because apparently I am the only poor fool on the face of this
earth that does not know how Windows maintains APIs.

Microsoft maintains them, and the Windows APIs are the basis of
Microsoft's success with backward compatibility. Now, they are
always expanding their APIs, and it's been argued that the MFC and
now .NET are the real APIs, but those both depend on the underlying
Win32 API, which remains unchanged.

The whole point of an API is that it has to be stable and
consistent.

[]

I come here, like many others, to gain (and make an attempt to
spread) knowledge and awareness. And, like many others, I would
prefer not to be treated as ignorant child due to the questions I
ask.

When you behave like a child, you get treated like one.

 

[dymondjack]

Thank you for the reply regarding the backward compatibility of APIs. This
is valuable information and clarifies much, and undoubtedly will have an
effect on how I write my code in the future.

There is one last thing that I would like to ask. I am relatively new to
posting (or trying to answer questions at least), and have tried to keep my
'posting ethics' in line. I will be the first to admit that occasionally I
give replies that are not as thourough as they probably should be, but all in
all I didn't think I was doing a bad job adhering to standard posting
guidelines.

David W. Fenton says:

When you behave like a child, you get treated like one.

I have read through my original post a number of times trying to find where
I 'behaved a child', but I cannot come up with anything. As I have every
interest to adhere to posting ethics within the community, I would like to
request that someone point out what it was I said that was 'off key' so that
I may remedy it in future posts.

I have included my original post below for review. Hopefully I can have
this matter cleared up as soon as possible. I do hope it was not in regards
the fact that I asked a question regarding envrion variables and their
stability (what purpose of this board, if I cannot ask questions). Possibly
it was because I forgot to explictly quote Mr. Fenton with the text that I
pulled from his post? Please advise.

Thank you.

I can certainly agree that most of the environ vars shouldn't be relied
upon, but I've always considered the "core" ones to be stable (USERNAME,
COMPUTERNAME, WINDIR, TEMP, TMP etc.)

I don't post this as an argument, but rather as a question. I've been of
the impression that windows and just about every program you install uses
these 'core' variables, and therefore assumed that changing them would
result
in serious errors throughout the system. Obviously, I've never tried
changing WINDIR to see what happens

Unfortunately, I know little about how windows and other programs stores
information such as this. I see these vars used numberous times in
various
batch files (both system created and user created).

Are the actual values that the OS and its apps use stored in the registry
as
opposed to envrion vars? In which case, I should be going to the reg for
the
values (or wherever they may be stored...)

If anyone could clarify, that would be great. I've always wondered about
this.

For the record, I've gone through these environ vars on Win2000, XP and
Vista (gotta love consistancy with OS versions in the workplace) and have
found that the var names that I do use are consistent between these three
OS
versions.

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[David W. Fenton]

=?Utf-8?B?ZHltb25kamFjaw==?= <dymondjack at hot mail dot com> wrote
in
[]

<PLONK>