Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA Update desktop shortcut (2000 SR1)

    I am setting up a macro-driven workbook that includes a button that saves the current file, resets all input data, and then saves the file under a new filename (using a calculated "Week Ending yymmdd" value that updates during the reset/save new process). I have this working better than I had expected, yet I can't figure out how to do one last task with this macro: Update a desktop shortcut from the prior filename to the new filename.

    The eventual users are accustomed to clicking their desktop icons to open specific files, since they generally work with only a few files and don't want to be bothered navigating Explorer windows to find their files. Also, since the process I have set up will actually be saving archival files, I want to ensure that by default they will be opening the correct file.

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>Can VBA be used to modify an existing shortcut to reference a different file? Or would there be an option to delete the existing shortcut and create a new shortcut?

    <img src=/w3timages/redline.gif width=33% height=2><pre>Private Sub CmdClearData_Click()

    'Reset all weekly data after confirm

    Application.ScreenUpdating = False

    Confirm = MsgBox("Are you sure you want to reset?", _
    257, "Caution!")

    If Confirm = 1 Then

    ActiveWorkbook.Save
    Worksheets("Mon").Range("Clear_Mon").Value = 0
    Worksheets("Tue").Range("Clear_Tue").Value = 0
    Worksheets("Wed").Range("Clear_Wed").Value = 0
    Worksheets("Thu").Range("Clear_Thu").Value = 0
    Worksheets("Fri").Range("Clear_Fri").Value = 0
    Worksheets("Sat").Range("Clear_Sat").Value = 0
    Range("StartDate").Select
    ActiveCell.Value = InputBox("Enter new date" & _
    " here", , ActiveCell.Value + 7, 3500, 3500)
    ActiveWorkbook.SaveAs (ActiveWorkbook.Path _
    & "" & Range("Agency").Value & " " & _
    Worksheets("Mon").Range("WE_Date").Value & _
    ".xls")

    Else: End If

    Application.ScreenUpdating = True

    End Sub</pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Update desktop shortcut (2000 SR1)

    David,

    The following should work provided you have not disabled the Windows Scripting Host.

    Dim WshShell, oLink
    Dim strDeskTop As String
    Set WshShell = CreateObject("WScript.Shell")
    strDeskTop = WshShell.SpecialFolders("Desktop")
    Set oLink = WshShell.CreateShortcut(strDeskTop & "<font color=red>My Shortcut</font color=red>.lnk")
    oLink.TargetPath = ActiveWorkbook.FullName
    oLink.Save

    This should change the actual filename referenced by an existing Link called

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Update desktop shortcut (2000 SR1)

    It works great! <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>

    I made one little change, to allow for the same variable-driven naming, so I can now copy/rename the file, change the value in the "Agency" cell, and have everything still fully-functional without having to re-edit the VBA to adjust for the different filename. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    <img src=/w3timages/redline.gif width=33% height=2><pre> Set oLink = WshShell.CreateShortcut(strDeskTop _
    & "" & Range("Agency").Value & ".lnk")</pre>

    <img src=/w3timages/redline.gif width=33% height=2>

Posting Permissions

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