You could use part of Jim's code to get the location of the desktop:

Option Explicit
Sub SaveToDesktop1()

Dim WSHShell As Object
Dim DesktopPath As String

Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")

ActiveWorkbook.SaveAs Filename:= _
DesktopPath & "\M1.txt", FileFormat:=xlText, _
CreateBackup:=False

End Sub

But after you save the activeworkbook as a .txt, you'll notice that the file
name has changed (as well as the worksheet name).

Another option is to copy that worksheet to a new workbook and save that copy.
Then the original file isn't disturbed:

Option Explicit
Sub SaveToDesktop2()

Dim WSHShell As Object
Dim DesktopPath As String

Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")

ActiveSheet.Copy 'to a new workbook

With ActiveSheet.Parent 'that new workbook
.SaveAs Filename:= _
DesktopPath & "\M1.txt", FileFormat:=xlText, _
CreateBackup:=False
.Close savechanges:=False
End With

End Sub

Mark wrote:

Handy code Jim, but not exactly waht i was after.this is what i currently
have recorded as macro code...

Sub Macro2()

' Macro2 Macro
' Macro recorded 13/08/2005 by Mark

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Mark\Desktop\M1.txt", FileFormat:=xlText, _
CreateBackup:=False
End Sub

But what falls down with this is if say Jim's computer is using it, it will
try to save to Mark's desktop and not Jims.I need a 'default' desktop
location for whoevers computer is running the sheet.

"Jim Cone" wrote:

Hello Mark,

The workbook must have been saved...
'--------------------
Sub Desktopshortcut()
Dim WSHShell As Object
Dim MyShortcut As Object
Dim DesktopPath As String

Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")
Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\" & _
ActiveWorkbook.Name & ".lnk")

With MyShortcut
.TargetPath = ActiveWorkbook.FullName
.IconLocation = "%SystemRoot%\system32\moricons.dll" 'or whatever
.WindowStyle = 1
.Save
End With

Set WSHShell = Nothing
Set MyShortcut = Nothing
MsgBox "A shortcut has been placed on your desktop. ", _
vbInformation, " Mark Did It"
End Sub
'--------------------

Jim Cone
San Francisco, USA


"Mark" wrote in message
...
Hi just a little help needed.
When i run a macro to save a sheet to my desktop as a text file it works
fine but in the code it has my desktop address ie.
c:\document...\Mark\Desktop, however i have a few people who will be using my
worksheet and i need it to save to there desktop, so i need the code to
basically save it to the 'default' desktop. is this possible? any ideas on
the code?
tks
Mark


--

Dave Peterson