Hi, just having trouble with saving an excel document on the desktop in Windows XP. As different users log in you cannot select the path see below with the username highlighted

C:\Documents and Settings\username\Desktop\

Is there a way in VBA where it just saves it on the desktop just for that user?

Thank you in advanced

You should be able to do it by using:

Dim strPath As String
Dim strUser As String

strUser = Environ("username")
strPath = "C:\Documents and Settings\" & strUser & "\Desktop\"

What to do if the OS can be either Win98 or WinXP for different end users ?
How to write the code which suits for both the OS ?

Environ("USERPROFILE") yields the location of the user profile, i.e. "C:\Documents and Settings\userName"

The Environ() function does not work in Win98. The following function should work in both Win98 and WinXP:

Public Function GetDesktopFolder() As String

Dim oSH As Object

Set oSH = CreateObject("WScript.Shell")
GetDesktopFolder = oSH.SpecialFolders("Desktop")
Set oSH = Nothing

End Function


See if this solution works for you.