create shortcut but not for active workbook
 

i got the following code to save a copy of the workbook. I tried to add a
"create shortcut" but it makes the shortcut for the activeworkbook and not
the copy of the workbook...

########################***####################### ####

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.Protect Password:=""



Dim bErr As Boolean
On Error Resume Next
MkDir "c:\maykent"
MkDir "C:\maykent\stocksheet"
bErr = (Err.Number < 0)
On Error GoTo 0
If bErr Then
If Dir("C:\maykent\stocksheet\LAST WEEK stocksheet.xls") < "" Then




ThisWorkbook.SaveCopyAs "C:\maykent\stocksheet\LAST WEEK stocksheet.xls"


CreateShortCut thisworkbook
###########################***#################### ####

&

##########################***##################### ####

Sub CreateShortCut(bk As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim sPathDeskTop As String


Set oWSH = CreateObject("WScript.Shell")
sPathDeskTop = oWSH.SpecialFolders("Desktop")


Set oShortcut = oWSH.CreateShortCut(sPathDeskTop & "\" & _
bk.Name & ".lnk")
With oShortcut
.TargetPath = bk.FullName
.Save
End With
Set oWSH = Nothing


End Sub

#######################***######################## ####
--
Helpful advice on posting to newsgroups here...
http://www.cpearson.com/excel/newposte.htm

create shortcut but not for active workbook
 

Not too sure exactly what it is that you want. Your active workbook never
changes by doing a savecopyas. Can you post the code that did not work for
you with a bit more of an expanation.
--
HTH...

Jim Thomlinson


"pswanie" wrote:

i got the following code to save a copy of the workbook. I tried to add a
"create shortcut" but it makes the shortcut for the activeworkbook and not
the copy of the workbook...

########################***####################### ####

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.Protect Password:=""



Dim bErr As Boolean
On Error Resume Next
MkDir "c:\maykent"
MkDir "C:\maykent\stocksheet"
bErr = (Err.Number < 0)
On Error GoTo 0
If bErr Then
If Dir("C:\maykent\stocksheet\LAST WEEK stocksheet.xls") < "" Then




ThisWorkbook.SaveCopyAs "C:\maykent\stocksheet\LAST WEEK stocksheet.xls"


CreateShortCut thisworkbook
###########################***#################### ####

&

##########################***##################### ####

Sub CreateShortCut(bk As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim sPathDeskTop As String


Set oWSH = CreateObject("WScript.Shell")
sPathDeskTop = oWSH.SpecialFolders("Desktop")


Set oShortcut = oWSH.CreateShortCut(sPathDeskTop & "\" & _
bk.Name & ".lnk")
With oShortcut
.TargetPath = bk.FullName
.Save
End With
Set oWSH = Nothing


End Sub

#######################***######################## ####
--
Helpful advice on posting to newsgroups here...
http://www.cpearson.com/excel/newposte.htm

create shortcut but not for active workbook
 

the people that use the computers from time to time delete any and everything
from the desktop. thus what i need is that once a week when the code runs.

1. check and if neede recreate folder
2.. it save and over right a copy (got that)
3. recreate short cut (need that)

but when i use the undermentiond part i get a short cut that points to the
wrong file...

the macro gets run from StockSheet.xls
and save a copy as Previous Week StockSheet.xls
in folder c:\maykent\stocksheets


CreateShortCut thisworkbook

create shortcut but not for active workbook
 

Forgive me I am nursing a cold at the moment so I might be missing something
here... Have you tried creating the shortcut just prior to the savecopyas...

CreateShortCut thisworkbook

ThisWorkbook.SaveCopyAs "C:\maykent\stocksheet\LAST WEEK stocksheet.xls"

--
HTH...

Jim Thomlinson


"pswanie" wrote:

the people that use the computers from time to time delete any and everything
from the desktop. thus what i need is that once a week when the code runs.

1. check and if neede recreate folder
2.. it save and over right a copy (got that)
3. recreate short cut (need that)

but when i use the undermentiond part i get a short cut that points to the
wrong file...

the macro gets run from StockSheet.xls
and save a copy as Previous Week StockSheet.xls
in folder c:\maykent\stocksheets


CreateShortCut thisworkbook