Create a desktop shortcut to an Excel file

Does anyone know how to use VBA to create a desktop[ shortcut to a specifice Excel file.
(There is a discussion about this in the knowledge base but the link is broken.)  Thanks.
davidamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jppintoCommented:
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
davidamAuthor Commented:
All of these look quite promising, but I cannot figure out how to make them work.  I would like to post some code and ask if anyone could adapt it to point to say a file called "Input.xls" in the C directory.  I just do not know what to change on these:
' and an even simpler one from skrga

Dim sShortcutPath As String, sExtension As String
Dim fs As New FileSystemObject
Dim oShell As New WshShell

sShortcutPath = InputBox("Enter path and filename of link file: ")
If sShortcutPath <> "" Then
   sExtension = fs.GetExtensionName(sShortcutPath)
   Select Case sExtension
      Case "lnk"
         Dim oShortcut As WshShortcut
         Set oShortcut =  oShell.CreateShortcut(sShortcutPath)
      Case "url"
	     Dim oURLShortcut As WshURLShortcut
         Set oURLShortcut = oShell.CreateShortcut(sShortcutPath)
      Case Else
         ' user input an invalid path or filename; display an error and 
         ' exit
         Exit Sub
   End Select 
End If

Open in new window

' a nice simple one from skrga

Dim WSHShell As Object
Set WSHShell = CreateObject("WScript.Shell")

Dim MyShortcut As Object
Dim DesktopPath As String

' Read desktop path using WshSpecialFolders object
DesktopPath = WSHShell.SpecialFolders("Desktop")

' Create a shortcut object on the desktop
Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & _
"\notepad_999.lnk")

' Set shortcut object properties and save it
MyShortcut.TargetPath = WSHShell.ExpandEnvironmentStrings("%windir%\notepa d.exe")
MyShortcut.WorkingDirectory = WSHShell.ExpandEnvironmentStrings("%windir%")
MyShortcut.WindowStyle = 4
MyShortcut.IconLocation = _
WSHShell.ExpandEnvironmentStrings("%windir%\notepa d.exe, 0")
MyShortcut.Save
' A nice simple one from skrga
Set MyShortcut = Nothing
Set WSHShell = Nothing

Open in new window

'From jppinto and skrga

Option Explicit 
 
Sub CreateDesktopShortcut() 
     ' =================================================================
     ' Create a custom icon shortcut on the users desktop
     ' =================================================================
     
     ' Msgbox string variables
    Dim szMsg As String 
    Dim szStyle As String 
    Dim szTitle As String 
     
     
     ' Change here for the icon's name
    Const szIconName As String = "\cvg.ico" 
     
     
     ' Constant string values, you can replace "Desktop"
     ' with any Special Folders name to create the shortcut there
    Const szlocation As String = "Desktop" 
    Const szLinkExt As String = ".lnk" 
     
     
     ' Object variables
    Dim oWsh As Object 
    Dim oShortcut As Object 
     
     
     ' String variables
    Dim szSep As String 
    Dim szBookName As String 
    Dim szBookFullName As String 
    Dim szPath As String 
    Dim szDesktopPath As String 
    Dim szShortcut As String 
     
     
     ' Initialize variables
    szSep = Application.PathSeparator 
    szBookName = szSep & ThisWorkbook.Name 
    szBookFullName = ThisWorkbook.FullName 
    szPath = ThisWorkbook.Path 
     
     
     
    On Error Goto ErrHandle 
     ' The WScript.Shell object provides functions to read system
     ' information and environment variables, work with the registry
     ' and manage shortcuts
    Set oWsh = CreateObject("WScript.Shell") 
    szDesktopPath = oWsh.SpecialFolders(szlocation) 
     
     
     ' Get the path where the shortcut will be located
    szShortcut = szDesktopPath & szBookName & szLinkExt 
     
     
     ' Make it happen
    Set oShortcut = oWsh.CreateShortCut(szShortcut) 
     
     
     ' Link it to this file
    With oShortcut 
        .TargetPath = szBookFullName 
        .IconLocation = szPath & szIconName 
        .Save 
    End With 
     
     
     ' Explicitly clear memory
    Set oWsh = Nothing 
    Set oShortcut = Nothing 
     
     
     ' Let the user know it was created ok
    szMsg = "Shortcut was created successfully" 
    szStyle = 0 
    szTitle = "Success!" 
    MsgBox szMsg, szStyle, szTitle 
     
     
    Exit Sub 
     
     
     ' or if it wasn't
ErrHandle: 
    szMsg = "Shortcut could not be created" 
    szStyle = 48 
    szTitle = "Error!" 
     
    MsgBox szMsg, szStyle, szTitle 
End Sub

Open in new window

0
 
jppintoCommented:
The code from the link I provided is to be run from an Excel sheet and will make a shortcut with the link to that file.
0
 
davidamAuthor Commented:
I figured as much but all I got was the ErrHandle msg.  I saved the file and gave it a name and got the same result. Any suggestions?
0
 
skrgaCommented:
You should set Reference to your vba project :
In vba window go to Tools > References and put check mark on Windows Scripting Host or similar (i have win7 + office2007 so i don't know is it called same at you but it should be similar)
 VBA
0
 
davidamAuthor Commented:
Further to previous comment to jppinto...sorry it does work. I would like to know how to adapt it to point to a file other than this workbook.
0
 
jppintoConnect With a Mentor Commented:
Just try changing this part of the code:

 ' Initialize variables
    szBookName = "Input Book"
    szBookFullName = "Input.xls"
    szPath = "C:\"

jppinto
0
 
skrgaCommented:
P.S. This solution worked at my pc (with reference checked)

Dim WSHShell As Object
Set WSHShell = CreateObject("WScript.Shell")

Dim MyShortcut As Object
Dim DesktopPath As String

' Read desktop path using WshSpecialFolders object
DesktopPath = WSHShell.SpecialFolders("Desktop")

' Create a shortcut object on the desktop
Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & _
"\TEST.lnk")

' Set shortcut object properties and save it
MyShortcut.TargetPath = WSHShell.ExpandEnvironmentStrings("D:\test.pdf") 'Path to file
MyShortcut.WorkingDirectory = WSHShell.ExpandEnvironmentStrings("D:\") ' path to working directory
MyShortcut.WindowStyle = 4
'MyShortcut.IconLocation = 'WSHShell.ExpandEnvironmentStrings("%windir%\notepa d.exe, 0") 'you can uncomment this and set icon for shortcut
MyShortcut.Save


Set MyShortcut = Nothing
Set WSHShell = Nothing
0
 
davidamAuthor Commented:
I actually was able to adapt one of skrga's solutions and ppinto's works as well.  Thanks to all; brilliant stuff!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.