great help here guys thanks heaps.dazed for days on this!
one other thing how do you get it to automatically overwrite the existing
file on my desktop? at the moment it prompts me to overwrite it or not.drives
me nuts.
tks again
mark

"Dave Peterson" wrote:

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