Solved

Create a desktop shortcut to an Excel file

Posted on 2011-02-16
11
780 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now