Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts

    desktop shortcut for workbook

    I have a spreadsheet that I will distribute on CD. I would like to put an automatic desktop shortcut in. That is on the first run install the desktop so the user can open from there in future.

  2. #2
    New Lounger
    Join Date
    Jun 2011
    Location
    Dorset, UK
    Posts
    6
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Well here's your starting point. This code will create a shortcut on the users desktop to the workbook, whenever the workbook is opened. If there already is one it just overwrites it.
    Code:
    Private Sub Workbook_Open()
        Dim Shl As Object, ShtCt As Object
        Dim WbkPth As String, WbkNm As String, strPath As String
        
        Set Shl = CreateObject("WScript.Shell")
        WbkPth = ThisWorkbook.Path
        WbkNm = ThisWorkbook.Name
        
        strPath = Shl.SpecialFolders("Desktop") & "\" & WbkNm & ".lnk"
        Set ShtCt = Shl.CreateShortcut(strPath)
        With ShtCt
            .TargetPath = WbkPth & "\" & WbkNm
            .Description = WbkNm
            .RelativePath = WbkPth
            .WorkingDirectory = WbkPth
            .Hotkey = ""
            .Save
        End With
    End Sub
    Clearly there's some redundancy here. I'm looking into how to detect if the shortcut already exists, I'll get back to you.
    K

    Software-Matters ~ Straightforward Solutions to Take Care of Your Business

  3. The Following User Says Thank You to Kafrin For This Useful Post:

    ozcee (2011-07-01)

  4. #3
    New Lounger
    Join Date
    Jun 2011
    Location
    Dorset, UK
    Posts
    6
    Thanks
    0
    Thanked 3 Times in 3 Posts
    OK, here you go:
    Code:
    Private Sub Workbook_Open()
        Dim Shl As Object, ShtCt As Object
        Dim WbkPth As String, WbkNm As String, strPath As String
        
        Set Shl = CreateObject("WScript.Shell")
        WbkPth = ThisWorkbook.Path
        WbkNm = ThisWorkbook.Name
        
        strPath = Shl.SpecialFolders("Desktop") & "\" & Left(WbkNm, InStrRev(WbkNm, ".") - 1) & ".lnk"
        If Dir(strPath) = "" Then
            Set ShtCt = Shl.CreateShortcut(strPath)
            With ShtCt
                .TargetPath = WbkPth & "\" & WbkNm
                .Description = WbkNm
                .RelativePath = WbkPth
                .WorkingDirectory = WbkPth
                .Hotkey = ""
                .Save
            End With
        End If
    End Sub
    This code now only creates the shortcut if it doesn't already exist. This is based on the file name, so if they have a shortcut to a file with the same name then it won't be overwritten.

    I also added the modification to the strPath line to take the Excel file extension off the name given to the link, as it's not needed.
    K

    Software-Matters ~ Straightforward Solutions to Take Care of Your Business

  5. The Following User Says Thank You to Kafrin For This Useful Post:

    ozcee (2011-07-01)

  6. #4
    New Lounger
    Join Date
    Jun 2011
    Posts
    9
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Kafrin
    thankyou so much. Works perfectly. I may have got there (in about 6 months).
    I had been pulling my hair out figuratively speaking of course for about 6 hours trying to get this worked out.
    You made it look so easy too.

  7. #5
    New Lounger
    Join Date
    Jun 2011
    Location
    Dorset, UK
    Posts
    6
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Your very welcome.

    To be honest I got the basics from internet searches - I've had a lot of practice at working out which terms to search on! From there though I modified it for your needs, which is largely down to experience. You'll pick it as you go - remember, you know more now than you did this morning :-)
    K

    Software-Matters ~ Straightforward Solutions to Take Care of Your Business

  8. The Following User Says Thank You to Kafrin For This Useful Post:

    ozcee (2011-07-01)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •