creating a shortcut in module

[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

 

[Bob Phillips]

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)

 

[Zygoid]

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

 

[Bob Phillips]

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)