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

 

[Leith Ross]

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

I have the code that will test to see if the directory exists an
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 tha
will
launch a specific workbook (namely the one that created th
directory).

Any help would be appreciated!

Thanks,
Ray

Hello Ray,

It would be nice to know the folder's name and the workbook's name th
shortcut is to launch. I would rather give you a code example using thi
information than a generic one

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[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

 

[Leith Ross]

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 \ServerNameLocation I can replac
it
with the applicable path.

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

Again, thanks for the response.

Regards,
Ray

--
"Trying to make reports so easy... even a monkey could run ''em!"



Ross' (http://www.thecodecage.com/forumz/members/leith-ross.html)
Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=52104)

Hello Ray,

I was out of my office for while. My apologies for the delay. You ca
change the values in the example code to match what you are using.
have never used this create a shortcut on a network, but Microsoft say
it can. We'll see.

============================================
'Written: January 19, 2009
'Author: Leith Ross

Sub MakeShortCut()

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

FolderPath = "C:\Documents and Settings\Admin.ADMINS\M
Documents\"
FileName = "Search Database"
FileType = ".xls"

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:\Program Files\"

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

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

Set objShell = Nothing

End Sub
===========================================

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[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