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,

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

"Charlotte E." wrote:

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

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

--

Gary
Excel 2003


"Charlotte E." <@ wrote in message
...
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,

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)




"Gary Keramidas" wrote in message
...
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\"

--

Gary
Excel 2003


"Charlotte E." <@ wrote in message
...
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,

Works - thanks :-)


Dave Peterson wrote:
Dim myPath As String
myPath = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
MsgBox myPath

"Charlotte E." wrote:

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,