Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut key for displaying the active cell comment | Excel Discussion (Misc queries) | |||
How to e-mail a shortcut to the active workbook | Excel Programming | |||
email shortcut to active workbook as an outlook attachment | Excel Programming | |||
Call macro from active workbook-duplicate shortcut keys | Excel Programming | |||
Shortcut to toggle active Worksheet | Excel Programming |