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

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

Ah!!!!! Thanks guys, that was another one on my list of long-forgotten 'Want to do's.

I'll definitely have a use for that.

Here's a slightly different question related to this though.

I wanted one of my macros to sense whether a workbook (or document in Word) was a version opened from an e-Mail attachment. It did this by doing an Instr() for "\Documents and Settings\" or "\Temporary Internet Files\" in the Fullname.

If returning a match, I would display a warning indicating to the user that they should open the original source file rather than making edits to the attached version as their changes would not be stored in the right place (a good idea you might think, but my users hated it because they believe changes SHOULD be made to e-Mail attachments rather than on the server; nothing like people who refuse to be taught ... but I digress)...

Anyway, I wanted to go a step further and provide a macro to run manually to do a FileSearch for folders in that user's D+S and Temp Internet folders, display them in a combobox so that they could review recently opened attachment files and re-open them, so that they could re-save them manually (less of a Big Brother approach). However I found that performing a FileSearch on these directories always seems to return no matches.
I don't know whether this is a Network Admin System Policy or whether files in these folders are tagged as 'System (Hidden) Files'.

In either case, is there any way around this?

BizMark

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

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

Mark,

Ok, then use this version...
'---------------------------
Sub SaveToDesktop()
Dim Wsh As Object
Dim strPath As String

Set Wsh = CreateObject("WScript.Shell")
strPath = Wsh.SpecialFolders.Item("Desktop")

ActiveWorkbook.SaveAs Filename:=strPath & "\" & "M1.txt", _
FileFormat:=xlText
Set Wsh = Nothing
End Sub
'---------------------------

Jim Cone
San Francisco, USA



"Mark" wrote in message
...
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

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

application.displayalerts = false
'whatever code you used to saveAs
application.displayalerts = true



Mark wrote:

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


--

Dave Peterson