File system shortcut resolves environment variable

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JonXL

Active Member
Joined
Feb 5, 2018
Messages
393
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Does splitting it up work? Something like... "%USER" & "PROFILE%" & ...
 

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 5, 2018
Messages
393
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What if you assigned it to a variable first?

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

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Tried that as well. And also using Replace to retrospectively insert %USERPROFILE% but TargetPath always resolved whatever and however the string was given.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,713
Messages
5,573,761
Members
412,550
Latest member
soking
Top