Desktop path?

[Charlotte E.]

I have Excel create a report (a .TXT-file) via VBA, and that part works fine


But now I would like Excel to automatically save this report on the users
desktop for easy access, everytime the user runs the macro.

But being in a multinational company the name and path of the users desktop
can be very different!

I've tried to see if there's an ENV-variable, telling me the current
desktop-path, but I couldn't find it, so...


....is there a way to find the current desktop path in VBA???


TIA,

 

[Dave Peterson]

Dim myPath As String
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
MsgBox myPath

 

[Gary Keramidas]

if you want to try the environment variable route, this may work. works in
english versions xp and vista. i think some have reported issues, but i've
never seen them.

environ("USERPROFILE") & "\Desktop\"

 

[Chip Pearson]

See http://www.cpearson.com/Excel/SpecialFolders.aspx for VBA code that will
return the full path of any of the Windows User's special folders (e.g.,
Desktop, Temp, My Documents, etc).


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Charlotte E.]

Works - thanks

Dim myPath As String
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
MsgBox myPath