Saving xls document from Access on Desktop

[Hemish]

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

 

[boblarson]

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\"

 

[geekay]

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 ?

 

[Bodisathva]

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

 

[ByteMyzer]

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.