?
Solved

Create a desktop shortcut to an Excel file

Posted on 2011-02-16
11
Medium Priority
?
844 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 1000 total points
ID: 34909185
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34909197
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

764 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