function for 'Application Data' folder as string?

[Gerrit Kiers]

Hi,

Does anyone has a function or a set of system variables for me that
defines the path of the 'Application Data' folder for current user in
a string?

GErrit

 

[Charles Maxson]

You can access those settings directly in the registry:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User
Shell Folders

 

[Tushar Mehta]

There's a lot of information available from the ENVIRON function -- use
as ENVIRON(<argument>). The following code gives you all the possible
values you can get:

Sub testIt()
Dim Indx As Long, EnvString As String
Indx = 1
Do
EnvString = Environ(Indx)
ActiveCell.Offset(Indx - 1, 0) = Environ(Indx)
Indx = Indx + 1
Loop Until EnvString = ""
End Sub

The code above puts the results in a column starting with the
activecell. Make sure the column is empty or the code will overwrite
whatever data are present in the column!

Once you run the above code you can decide what exactly you really
want:

ALLUSERSPROFILE
APPDATA
CommonProgramFiles
COMPUTERNAME
ComSpec
HOMEDRIVE
HOMEPATH
LOGONSERVER
NUMBER_OF_PROCESSORS
OS
Path
PATHEXT
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramFiles
SESSIONNAME
SystemDrive
SystemRoot
TEMP
TMP
USERDOMAIN
USERNAME
USERPROFILE
windir

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Winter]

One thing to remember is that most of these variables are only available on
NT/2000/XP machines. 95/98/ME do NOT have most of these.

-Tom

 

[Martin Seelhofer]

Hello there

Another possibility is to use the API-function SHGetFolderPath which
became available with IE5 (even on Win9x-systems) [mind the line
breaks!]:

Declare Function SHGetFolderPath Lib "shell32.dll" Alias "SHGetFolderPathA"
_
(ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, _
ByVal dwFlags As Long, ByVal lpszPath As String) As Long

' Return values
Const S_OK = &H0 ' Success
Const S_FALSE = &H1 ' Error occured
Const E_INVALIDARG = &H80070057 ' Wrong Parameter values

' Special folder constants
' [...] there are many others, ask if you need the full list
Const CSIDL_APPDATA As Long = &H1A
Const CSIDL_COMMON_APPDATA As Long = &H23
Const CSIDL_DESKTOP As Long = &H0
Const CSIDL_LOCAL_APPDATA As Long = &H1C
Const CSIDL_MYDOCUMENTS As Long = &HC
Const CSIDL_MYPICTURES As Long = &H27
Const CSIDL_PERSONAL As Long = &H5
Const CSIDL_PROGRAM_FILES As Long = &H26
Const CSIDL_WINDOWS As Long = &H24
' [...] there are many others, ask if you need the full list

' the API-wrapper
Function GetSpecialFolder(id As Long) As String
Dim res As String
res = String(255, 0)
If SHGetFolderPath(0, id, 0, 0, res) = S_OK Then
GetSpecialFolder = res
End If
End Function

' And a short demo:
Sub GetSpecialFolderDemo()
MsgBox GetSpecialFolder(CSIDL_APPDATA)
End Sub



Cheers,
Martin