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
--
"Trying to make reports so easy... even a monkey could run ''em!"

RayportingMonkey;188988 Wrote:
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
--
"Trying to make reports so easy... even a monkey could run ''em!"

Hello Ray,

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52104

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

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


"Leith Ross" wrote:


RayportingMonkey;188988 Wrote:
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
--
"Trying to make reports so easy... even a monkey could run ''em!"

Hello Ray,

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52104

RayportingMonkey;189163 Wrote:
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 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

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


"Leith Ross" wrote:


RayportingMonkey;188988 Wrote:
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
--
"Trying to make reports so easy... even a monkey could run ''em!"

Hello Ray,

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


--
Leith Ross

Sincerely,
Leith Ross

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

------------------------------------------------------------------------
Leith Ross's Profile: 'The Code Cage Forums - View Profile: Leith
Ross' (http://www.thecodecage.com/forumz/me...eith-ross.html)
View this thread: 'Create A Windows Shortcut To a .xls File? - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=52104)



Hello Ray,

I was out of my office for while. My apologies for the delay. You can
change the values in the example code to match what you are using. I
have never used this create a shortcut on a network, but Microsoft says
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\My
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 & FileName
& ".lnk")

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

Set objShell = Nothing

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52104

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
--
"Trying to make reports so easy... even a monkey could run ''em!"