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

[opinionated86]

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.

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

 

[edmitchell]

Hi

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

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]

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