Solved

Create a desktop shortcut to an Excel file

Posted on 2011-02-16
11
808 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:davidam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34909176
0
 
LVL 5

Accepted Solution

by:
skrga earned 250 total points
ID: 34909185
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34909197
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:davidam
ID: 34909504
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
 
LVL 33

Expert Comment

by:jppinto
ID: 34909542
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
 

Author Comment

by:davidam
ID: 34909588
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
 
LVL 5

Expert Comment

by:skrga
ID: 34909642
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
 

Author Comment

by:davidam
ID: 34909646
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
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 250 total points
ID: 34909690
Just try changing this part of the code:

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

jppinto
0
 
LVL 5

Expert Comment

by:skrga
ID: 34909697
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
 

Author Closing Comment

by:davidam
ID: 34909735
I actually was able to adapt one of skrga's solutions and ppinto's works as well.  Thanks to all; brilliant stuff!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question