Create A Windows Shortcut To a .xls File?

  • Thread starter Thread starter RayportingMonkey
  • Start date Start date
R

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
 
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
 
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
 
Back
Top