Creating destop shortcut automatically

[Jas]

Hello,

I have not had formal training in VBA and am learning as the need arises.

I have a requirement for an Excel VBA procedure to create desktop shortcut to excel file named after the username e.g. Joe Bloggs.

I am thinking something like this: I will have a list of login users ids and full names in a hidden sheet in a workbook. I can e-mail this workbook to selected users. The user will open the workbook and press a button to create the shortcut My procedure will need to extract the login user id. Using the extracted login id, read the corresponding record from the list in my sheet to get the full 'user name'. Then create a shortcut to the file with the same name as the 'user name' and copy to desktop.

Thank you for any help and advice..

(When I have the above function working, I suppose I could then think about automating the e-mail step as well)

 

[sn152]

Hi,

I am not completely clear about what you want. But if it is to create a shortcut for your excel file. Then you can use the following code.

Sub CreateShortCut()
Dim oWSH As Object
Dim oShortcut As Object
Dim sPathDeskTop As String

Set oWSH = CreateObject("WScript.Shell")
sPathDeskTop = oWSH.SpecialFolders("Desktop")

Set oShortcut = oWSH.CreateShortCut(sPathDeskTop & "\" & _
ActiveWorkbook.Name & ".lnk")
With oShortcut
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set oWSH = Nothing

End Sub

 

[prasaddn]

Hi Jas,
If you are looking to get the windows log in ID, then below line in your VBA code will fetch you the details.

Environ("username")

You can assign this to any string variable or use to verify the your list.

Regards,
Prasad DN

 

[Jas]

Thank you for your earlier replies.

I have an excel workbook called Stats containing macros. I create a shortcut to this file from the right-click menu and then copy the shortcut to the desktop. This shortcut opens the file as expected BUT without the macro security warning prompt.

However, if I open the same file using a shortcut created with a VBA macro as suggested above, I always get the macro security warning prompt on first open. (After the macros are enabled, subsequent opens are without the security warning).

I am trying to find out how I can stop the security warning prompt when opening Stats with shortcut created by VBA macro?

For information: Each user will have a copy of Stats in the own folder. The reason for creating shortcut with vba macro is that not all users are comfortable with having to manually navigate to the network folder where the file resides. My intent is to automate this process by emailing the 'create shortcut' workbook to the user. When they open this workbook, shortcut to their copy of Stats will be created on their desktop.

 

[Jas]

I have resolved my issue by ensuring I am using a Trusted location.