Resolve changed filename in Excel shortcut Target attribute

Hi all,

I am working with a disorganized filesystem where file contents and filenames get changed manually by multiple users. As a result I have built a warehouse of shortcuts that point to the appropriate files. However when users change the name of the Target files (while I am still able to open the Target file with VBA using the shortcut path) the Target attribute of the shortcut does not get updated. The problem arises in another part of my code where I call the Target filepath and filename from the shortcut using the below code:

#Function for returning the Target attribute of the shortcut in string form
#Target attribute is a full filepath i.e. "E:\xample\targetfile.xls"
Function getTargetPath(ByVal Lnk As String)
On Error Resume Next
With CreateObject("Wscript.Shell").CreateShortcut(Lnk)
getTargetPath = .targetPath
.Close
End With
End Function

#code which assigns the filename of the targetPath to variable "fileName", using Target attribute pulled from above function
Set fso = CreateObject("Scripting.FileSystemObject")
fileName = fso.GetFileName(getTargetPath("E:\xample\shortcut.lnk"))

#code which does not work as "fileName" is the old filename, and not name updated by some other user
Dim someBook As Excel.Workbook: Set someBook = Excel.Workbooks(fileName)


I've read that Windows only resolves the Target attribute of the shortcut when it is manually double-clicked, and unsuccessfully explored this proposed solution.

Unfortunately clamping down on user permissions is not a viable solution in this scenario. My question is: Is there some way for me to return the current filename of the file when given access to the shortcut in VBA? Alternatively perhaps I could access the updated filename of the Target through an alternate method as the Target file will be open while the Sub is running. However there may be multiple Workbooks open, users may already have the Target open at time the Sub is run, and so I see no clear way to identify the correct open Workbook name to assign to "fileName".

Any help is much appreciated!

I'm a goof - lesson is to read docs more closely. Workbooks.Open method returns the name of the opened file even if it gets passed a shortcut.

#open shortcut Target file and assign Target filename to "fileName" variable in one line.
Dim fileName as Excel.Workbook: Set fileName = Workbooks.Open("E:\xample\shortcut.lnk")