creating shortcut of current excel file in startup folder using VBA - getting error saying can't save

opinionated86

New Member
Joined
Feb 2, 2016
Messages
12
I've created the following macro (from bits and bobs on forums) to save a shortcut of the current file in the startup folder of my computer, if i just manually create a shortcut it works so i know i can edit this folders contents but when i run this macro it produces and error about not being able to save shortcut. Any help would be great.

Code:
Sub new_flexi_user()startup = "C:\Users\" & UserName & "\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\" & Replace(ThisWorkbook.Name, ".xlsm", ".lnk")


Dim sShortcutLocation As String


sShortcutLocation = startup
With CreateObject("WScript.Shell").CreateShortcut(sShortcutLocation)
    .TargetPath = ThisWorkbook.FullName
    .Description = "Shortcut to the file"
    .Save
End With


End Sub

Thanks
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

edmitchell

Board Regular
Joined
Dec 18, 2017
Messages
50
Hi

from the code posted, the problem lies with the reference to 'USERNAME' (which isn't set). revised code follows (which works for me!)

Code:
Sub new_flexi_user()

startup = "C:\Users\" & Environ("UserName") & "\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\" & Replace(ThisWorkbook.Name, ".xlsm", ".lnk")

Dim sShortcutLocation As String

sShortcutLocation = startup
With CreateObject("WScript.Shell").CreateShortcut(sShortcutLocation)
    .TargetPath = ThisWorkbook.FullName
    .Description = "Shortcut to the file"
    .Save
End With

End Sub
 

opinionated86

New Member
Joined
Feb 2, 2016
Messages
12
Sorted now, hate to admit it but you were totally right, ditsy me, i have 'username' as a public function on most of my projects but hadn't created it on this one.
Thanks
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,792
Messages
5,574,322
Members
412,587
Latest member
Krucial155
Top