MS-Access and Creating Desktop Shortcuts.

Introduction.

The CreateShortcut() method of the Windows Script Object can be used to create Desktop shortcuts directly from Microsoft Access. These shortcuts can launch frequently used files—such as Access databases, Excel workbooks, Word documents, text files, and more—right from the Desktop. This concept is familiar, but the question is: how do we implement it within Access?

Earlier, we explored the Popup() method of the Windows Script Object to design a custom Message Box in Access that automatically closes after a specified duration. Unlike the standard Access MsgBox, which always requires a user click to proceed, our new MesgBox() function provides greater flexibility. Hopefully, you’ve already tested it and started applying it in your projects.

The VBA ShortCut() Function Prototype.

The simple VBA Function Code that creates a Desktop Shortcut is provided below for your review. All the required parameters are given as constants in the Function for clarity.

Public Function ShortCut()
Dim objwshShell As Object
Dim objShortcut As Object

Set objwshShell = VBA.CreateObject("WScript.Shell")
Set objShortcut = objwshShell.CreateShortCut("C:\Users\User\Desktop\Hello.txt.lnk")
With objShortcut
    .TargetPath = "C:\Windows\Notepad.exe "
    .Arguments = "D:\Docs\Hello.txt"
    .WorkingDirectory = "D:\Docs"
    .Description = "Opens Hello.txt in Notepad"
    .HotKey = "Ctrl+Alt+9"
    .IconLocation = "C:\Windows\System32\Shell32.dll,130"
    .WindowStyle = 2
    .Save
End With
End Function

You can create a Desktop Shortcut using the VBA code shown earlier, with just a few adjustments to the highlighted parameter values:

1. Replace “User” with your own Windows User Name in the path.

2. Create a simple text file (e.g., Hello.txt) containing any text and save it in one of your folders.

3. Update the Arguments value in the code with the correct file path of your Hello.txt file.

4. Set the Working Directory parameter to the folder where your file is saved.

5. Let the remaining parameter values stay as they are.

As an additional feature, the HotKey combination Ctrl+Alt+9 will be assigned to the shortcut. Pressing this key sequence will launch the Desktop Shortcut and open the file for editing.

The Desktop Shortcut Icon.

In the IconLocation parameter, notice the number 130 at the end. This number determines the icon that will appear on your Desktop Shortcut.

The numeric range is 0 to 305, each corresponding to a different icon available in the system library.

For example, the value 130 produces the following icon image:

You can also change the Shortcut Icon manually at any time:

1. Right-click on the Desktop Shortcut and select Properties from the menu.

2. On the Shortcut tab, click the Change Icon… button.

3. Browse through the available icons, select the preferred one, and click OK.

4. Finally, click Apply to update the Shortcut with the new icon.

Icon Images List.

When you open the Change Icon window, it displays about 76 columns with 4 icons each.
To identify the numeric index of a particular icon, start counting from the top-left icon, moving left to right. Multiply the column count by 4, and you’ll get the approximate icon number.

For example:

• The first column (4 icons) corresponds to numbers 0–3.

• The second column corresponds to 4–7.

• The third column corresponds to 8–11, and so on.

This manual counting method is the only reliable way I’ve found to determine the correct icon index number.
(See the reference image below for a clearer understanding.)

The DesktopShortcut() Function.

We are now ready to create our VBA function that generates a desktop shortcut.
This function requires at least three parameters, which must be supplied at the time of the call.
Based on these inputs, it will build and save a shortcut on the desktop.

The complete VBA code for the function is given below:

Option Compare Database
Option Explicit


Public Function DesktopShortCut(ByVal strShortCutName As String, _
ByVal strProgramPath As String, _
ByVal strFilePath As String, _
Optional strWorkDirectory As String = "", _
Optional ByVal strHotKey As String = "") As Boolean

On Error GoTo DesktopShortCut_Err
'-----------------------------------------------------------------
'Function: DesktopShortCut()
'Author: a.p.r. pillai
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'Remarks: You may modify the Code, but need to keep these
'Rem lines intact.
'Parameters
'-----------------------------------------------------------------
'1. Shortcut Name: Shows below the Desktop Icon
'2. strProgramPath: e.g.: "C:\Windows\System32\Notepad.exe"
'3. strfilePath: File PathName to Open, e.g. "D:\Docs\Helloworld.txt"
'4. Optional strWorkDirectory: e.g. "D:\Docs"
'5. Optional strHotKey: Quick Launch - e.g. Ctl+Alt+9: 1-9,A-Z
'-----------------------------------------------------------------
Dim objwshShell As Object
Dim objShortcut As Object
Dim strPath As String
Dim strProg As String, a As String, b As String
Dim strTemp As String
Dim DeskPath As String
Dim strmsg As String
Dim badchar As String, Flag As Boolean
Dim j, count As Integer

strPath = Environ("Path")

'Validation Checks
GoSub IsValidName
GoSub ValidateParams

'Find Current User Desktop
strTemp = Mid(strPath, InStr(1, strPath, "C:\Users\"), 25)
DeskPath = "C:\Users\" & Mid(strTemp, 10, InStr(10, strTemp, "\") - 10) & "\Desktop\"
DeskPath = DeskPath & strShortCutName & ".Lnk"

Set objwshShell = VBA.CreateObject("WScript.Shell")
Set objShortcut = objwshShell.CreateShortCut(DeskPath)
With objShortcut
If InStr(1, Trim(strProgramPath), " ") > 0 Then
    .TargetPath = Chr(34) & Trim(strProgramPath) & Chr(34) '="C:\Windows\Notepad.exe"
Else
    .TargetPath = Trim(strProgramPath)
End If
If InStr(1, Trim(strFilePath), " ") > 0 Then
    .Arguments = Chr(32) & Chr(34) & strFilePath & Chr(34) '="D:\Docs\Hello.txt"
Else
    .Arguments = Chr(32) & strFilePath '="D:\Docs\Hello.txt"
End If
'Optional Working Directory
 If Len(strWorkDirectory) > 0 Then
    .WorkingDirectory = strWorkDirectory '="D:\Docs"
 End If
 'Optional Keyboard HotKey
 If Len(Nz(strHotKey, "")) > 0 Then
    .HotKey = "Ctrl+Alt+" & strHotKey '= "Ctrl+Alt+K"
 Else
    .HotKey = ""
 End If
    .IconLocation = "C:\Windows\System32\Shell32.dll,130" '0 - 305
    .WindowStyle = 2
    .Save
End With
DesktopShortCut = True

DesktopShortCut_Exit:
Exit Function

IsValidName:
Flag = True
badchar = "\/:*?" & Chr(34) & "<>|"
count = 0
For j = 1 To Len(strShortCutName)
    If InStr(1, badchar, Mid(strShortCutName, j, 1)) Then
        count = count + 1
    End If
Next
Flag = IIf(count, False, True)
If Not Flag Then
    MsgBox "Shortcut Name: " & strShortCutName & vbCr & vbCr _
    & "Contains Invalid Characters." & vbCr & vbCr _
    & "*** Program Aborted. ***", , "DeskShortCut()"
    
    DesktopShortCut = False
    Exit Function
End If
Return

ValidateParams:
strmsg = ""
'Program Path
If Len(Nz(strProgramPath, "")) > 0 Then
   'Check whether the Program exists in the given path
   If InStr(1, strProgramPath, Dir(strProgramPath)) = 0 Then
     strmsg = "Program Path: " & strProgramPath & " Invalid."
   End If
Else
   strmsg = "Program Path: Not found!"
End If
'File Path
If Len(Nz(strFilePath, "")) > 0 Then
   If InStr(1, strFilePath, Dir(strFilePath)) = 0 Then
     If Len(strmsg) > 0 Then
        strmsg = strmsg & vbCr & "File Path: " & strFilePath & " Invalid."
     Else
        strmsg = "File Path: " & strFilePath & " Invalid."
     End If
   End If
Else
    If Len(strmsg) > 0 Then
        strmsg = strmsg & vbCr & "File Path: Not found!"
    Else
        strmsg = "File Path: Not found!"
    End If
End If
If Len(strmsg) > 0 Then
    MsgBox strmsg, , "DeskShortCut()"
    DesktopShortCut = False
    Exit Function
End If
Return

DesktopShortCut_Err:
MsgBox Err & " : " & Err.Description, , "DesktopShortCut()"
DesktopShortCut = False
Resume DesktopShortCut_Exit
End Function

The DesktopShortcut() function is defined with five parameters, of which the last two—Working Directory and HotKey—are optional.

To ensure reliability, validation checks have been included for the parameter values, along with error-trapping routines. These safeguard the function from unexpected issues and allow it to exit gracefully without crashing. Demo Run of the DesktopShortcut() Function.

The sample Run of the Function from the Immediate Window is given below:

Sample Run-1.

DesktopShortcut "HelloMyDB","C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE","D:\New Folder\ClassDB.accdb"

Sample Run-2.

DesktopShortcut "HelloMyDoc","C:\Program Files (x86)\Microsoft Office\Office12\WINWORD.EXE","D:\Docs\TelNo2411808.docx","D:\Docs","T"

Download DeskShortcut.zip

The TreeView Control Tutorial Session Links.

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2
5. TreeView Control Check-Mark Add Delete Nodes
6. TreeView ImageCombo Drop-Down Access Menu
7. Re-arrange TreeView Nodes by Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events
10. TreeView Control With Subforms

Read More