Excel

Create a Custom Desktop Shortcut

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Justinlabenne

Description:

Uses an Icon file for a shortcut in place of the standard "Workbook" icon. The example provided contains an Excel file, and an .ico (icon) file. 

Discussion:

If you design your projects in such a way that Excel's environment is customized extensively (IE: Application Icon, Hidden toolbars / command bars, etc..) this code allows you to use a custom icon (.ico file) to create a desktop shortcut back to your Excel workbook. 

Code:

instructions for use

			

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

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Go to TOOLS > MACRO > MACROS >
  2. When the dialog appears, select {CreateDesktopShortcut}
  3. Press {Run}
  4. You will be prompted if the shortcut was created successfully or not
  5. If it was, close the Excel file
  6. Use the newly created desktop shortcut to re-open the Excel file that contains the code
 

Sample File:

Create S-Cut.zip 15.44KB 

Approved by mdmackillop


This entry has been viewed 269 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express