Returning the Desktop Path

[BrianCP]

How can I return the desktop path?

I have a macro that imports and parses a desktop text file in Excel. The macro is tied to a button within a macro workbook which will be distributed to other users that have different desktop addresses than mine. Hence, they either would have to change the VBA script manually - not desirable, or possibly I can run some script that will return the desktop path?

Please advise.


Thanks!


Brian

[Paul_Hossler]

There's a bunch of Namespace options, but this gets the current user's desktop

Option Explicit

Sub test()
    MsgBox Desktop_Path
End Sub

Function Desktop_Path() As String
    Const sf_DESKTOP As Variant = 0
    Desktop_Path = CreateObject("Shell.Application").Namespace(sf_DESKTOP).Self.Path
End Function

[snb]

or

msgbox CreateObject("wscript.shell").specialfolders(4)

[mikerickson]

To be cross-platform

Function Desktop_Path() As String
    Const sf_DESKTOP As Variant = 0
    #If Mac Then
        Desktop_Path = Mid(MacScript("tell application ""Finder""" & vbLf & "return desktop as alias" & vbLf & "end tell"), 7)
    #Else
        Desktop_Path = CreateObject("Shell.Application").Namespace(sf_DESKTOP).Self.Path
    #End If
End Function