creating a shortcut in module

  • Thread starter Thread starter Zygoid
  • Start date Start date
Z

Zygoid

Hi All,

right now i use this simple module to "save_as"

Sub Save_As_FileName()

FName1 = Range("d2").Value
FName2 = Range("d3").Value
FName3 = Range("d5").Value
Pth = "LOCATION"
ActiveWorkbook.SaveAs Filename:=Pth & FName1 & FName2 & FName3
".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End Sub


is there a way i can have this module create a short cut in anothe
directory at the same time
 
Here's a very simple routine based upon an idea from John Green that can
create either a folder or file shortcut

Sub CreateShortCut(Shortcut As String, FileDir As String)
Dim wsh As Object
Dim oShortcut As Object

Set wsh = CreateObject("WScript.Shell")
Set oShortcut = wsh.CreateShortCut(Shortcut & ".lnk")
With oShortcut
.TargetPath = FileDir
.Save
End With
Set wsh = Nothing
End Sub

Shortcut is the name of the shortcut including path, FileDir is the shortcut
target.

You would use like so

CreateShortCut "C:\Documents and Settings\Bob\Desktop\myTest","C:\myTest"
to create a shortcut to a folder, or
CreateShortCut "C:\Documents and
Settings\Bob\Desktop\myTest","C:\myTest\Bob.xls"
to create a shortcut to a file

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
but both will vary depending on what values are placed in cells d2, d
and d5

I have tried this, but no go. :-(


Sub Save_As_FileName()

FName1 = Range("d2").Value 'shows sales rep initials
FName2 = Range("d3").Value 'shows customer name
FName3 = Range("d5").Value 'shows job location
pth = "c:\greg\bids\" 'file sorted by customer name

ActiveWorkbook.SaveAs Filename:=pth & FName2 & FName3 & FName2
".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End Sub

'now i would like to put a shortcut into the sales reps file folder

Sub CreateShortCut(Shortcut As String, FileDir As String)
Dim wsh As Object
Dim oShortcut As Object

FName1 = Range("d2").Value 'shows sales rep initials
FName2 = Range("d3").Value 'shows customer name
FName3 = Range("d5").Value 'shows job location
pth = "c:\greg\reps\" ' has a file for each rep, fil

name is the rep
initials


Set wsh = CreateObject("WScript.Shell")
Set oShortcut = wsh.CreateShortCut(pth & FName3 & Fname5 & ".lnk")
With oShortcut
.TargetPath = pth & FName1
.Save
End With
Set wsh = Nothing
End Su
 
As you ar getting the values from a worksheet, you don.t need the sub
arguments. Also, you lost the . (dot) before tTargetPath and Save. Try this
exactly as given

Sub CreateShortCut()
Dim wsh As Object
Dim oShortcut As Object

FName1 = Range("d2").Value 'shows sales rep initials
FName2 = Range("d3").Value 'shows customer name
FName3 = Range("d5").Value 'shows job location
pth = "c:\greg\reps\" ' has a file for each rep, file

Set wsh = CreateObject("WScript.Shell")
Set oShortcut = wsh.CreateShortCut(pth & FName3 & Fname5 & ".lnk")
With oShortcut
.TargetPath = pth & FName1
.Save
End With
Set wsh = Nothing
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top