Create A Windows Shortcut To a .xls File?

[RayportingMonkey]

I need to create directory on a given user's c: when a specific workbook is
opened and leave a windows shortcut in that new directory.

I have the code that will test to see if the directory exists and prompts
the user for permission to create C:\Share if it doesn't exist.

What I need help with is creating a windows shortcut in C:\Share that will
launch a specific workbook (namely the one that created the directory).

Any help would be appreciated!

Thanks,
Ray

 

[RayportingMonkey]

Hey Leith,

Thanks for the response.

The location where the windows shortcut will reside is simply C:\Share.

As for the path to the actual workbook, it is a very long UNC path. Suffice
to say that if you reference it as \\ServerName\Location\ I can replace it
with the applicable path.

The specific filename can also be "filename.xls" or something generic as I
will be using this for more than one instance anyway.

Again, thanks for the response.

Regards,
Ray

 

[RayportingMonkey]

Hey Leith,

Thanks - It worked like a charm!

For the purpose of this thread, here's how I am running it in my environment:

Dim FileName As String
Dim FolderPath As String
Dim objShell As Object
Dim objShortCut As Object
Dim ShortcutFolder As String

FolderPath = Application.ThisWorkbook.Path & "\"
FileName = Application.ThisWorkbook.Name

Set objShell = CreateObject("WScript.Shell")

'If the folder is a virtual folder then use this syntax
'ShortcutFolder = objShell.SpecialFolders("Desktop") & "\"

'If the folder has a physical location then use this syntax
ShortcutFolder = "C:\Share\"

Set objShortCut = objShell.CreateShortcut(ShortcutFolder & FileName & ".lnk")

With objShortCut
..TargetPath = FolderPath & FileName
..WindowStyle = 1 'Normal focus
..Save
End With

Set objShell = Nothing


The end users will get an email with all the necessary workbook links in it,
so when the launch the workbooks, this script will run from the On-Open
event. And because the shortcut being created points to the active workbook,
I was simply able to use the ThisWorkbook property instead of hard-coding the
information.

Again, thanks for your help!!!

Later-
Ray