save as - to any desktop
 

Hello,
The code below works for me, but how do I :
a.) Make this WB "save as" a copy to any individuals desktop? in other
words what can I use for a wildcard for the " XXX" in XXX.TRGROUP. (XXX would
be a name like jsmith, jdoe etc)
and b.) How can this be done as soon as the WB is opened from the original
read only file, so once opened, it saves a copy directly to the PC's desktop
- without prompting-?


ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\XXX.TRGROUP\Desktop\copy of.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


Your help is as always greatly appreciated.
Thank you
Driftwood

save as - to any desktop
 

DeskTop = environ("UserProfile")

will give

"C:\Documents and Settings\jsmith


DeskTop = environ("UserProfile")
bkname = ".TRGROUP\Desktop\copy of.xls"
ActiveWorkbook.SaveAs Filename:= DeskTop & bkname, _
FileFormat:= xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

"Driftwood" wrote:

Hello,
The code below works for me, but how do I :
a.) Make this WB "save as" a copy to any individuals desktop? in other
words what can I use for a wildcard for the " XXX" in XXX.TRGROUP. (XXX would
be a name like jsmith, jdoe etc)
and b.) How can this be done as soon as the WB is opened from the original
read only file, so once opened, it saves a copy directly to the PC's desktop
- without prompting-?


ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\XXX.TRGROUP\Desktop\copy of.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


Your help is as always greatly appreciated.
Thank you
Driftwood

save as - to any desktop
 

Thank You Joel.
Took me a few minutes to realize that the ".TRGROUP" was actually part of
the UserProfile I had to delete it because it was trying to use
"jsmith.TRGROUP.TRGROUP"
So now it is:
bkname = "\Desktop\copy of.xls"
And works just GREAT!
Cheers to all the great responders out here!
Driftwood

"Joel" wrote:

DeskTop = environ("UserProfile")

will give

"C:\Documents and Settings\jsmith


DeskTop = environ("UserProfile")
bkname = ".TRGROUP\Desktop\copy of.xls"
ActiveWorkbook.SaveAs Filename:= DeskTop & bkname, _
FileFormat:= xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

"Driftwood" wrote:

Hello,
The code below works for me, but how do I :
a.) Make this WB "save as" a copy to any individuals desktop? in other
words what can I use for a wildcard for the " XXX" in XXX.TRGROUP. (XXX would
be a name like jsmith, jdoe etc)
and b.) How can this be done as soon as the WB is opened from the original
read only file, so once opened, it saves a copy directly to the PC's desktop
- without prompting-?


ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\XXX.TRGROUP\Desktop\copy of.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


Your help is as always greatly appreciated.
Thank you
Driftwood