File system shortcut resolves environment variable

[ExcelGzh]

I use an Excel spreadsheet to create Windows file system shortcuts. The spreadsheet has 3 columns. shortcut name, shortcut folder and target folder. A vba routine then runs through the list and creates or recreates the shortcuts. If the target moves - and it frequently does at my workplace - I can change the target name and re-run the vba routine. I have used this routine successfully for quite a while in a mapped network drive environment. We have recently started using Microsoft Teams and we access the documents by synchronising the Teams channels to the local computer. This means all documents have the path <userprofile>\<path>\<document>. This then means that all shortcuts have to be delivered to Teams in a way that takes account of the user profile path. The code I am using is:

sub CreateTeamsShortcuts
Dim MyShortcut as Object
Dim WSHShell
Set WSHShell = CreateObject("WScript.Shell")
Set MyShortcut =WSHShell.CreateShortcut ("%USERPROFILE%\" & <shortcutfolder>, <shortcutname>)
MyShortcut.TargetPath = "%USERPROFILE%\" & <targetfolder>
MyShortcut.save
End sub

The problem I have is that while CreateShortcut retains the environment variable USERPROFILE in the text string and places the shortcut in the correct place, the TargetPath does not. It always resolves the text string to my own user profile which of course is useless for anyone else attempting to use the shortcut.

Is there a way of preventing TargetPath from resolving the environment variable USERPROFILE and just placing the original text string in the shortcut properties?

 

[JonXL]

Does splitting it up work? Something like... "%USER" & "PROFILE%" & ...

 

[ExcelGzh]

No that didn't work. I've also tried "C:\Users\%USERNAME%" and also put the whole string inside another pair of double quotes. It was even able to resolve """%USERPROFILE%\""" & <shortcutfolder>, <shortcutname>)

 

[JonXL]

What if you assigned it to a variable first?

Dim myTargetPath
myTargetPath = "%USERPROFILE%\" & <targetfolder>
MyShortcut.TargetPath = myTargetPath

 

[ExcelGzh]

Tried that as well. And also using Replace to retrospectively insert %USERPROFILE% but TargetPath always resolved whatever and however the string was given.