I'm having a minor but annoying hiccup on a patch of code. I have cobbled it together from various googlings, but it doesn't function. The goal is to select a file and then to replace the existign file link with the new file selected. If replace 'ExistingLink' in the last line of code and manually type in the URL; then it works. If I run it as is, it says that it's mismatched (ie. ExistingLink does not match what the existing link actually is)
Code:
Option Explicit
Dim myFilePath As Variant
Dim ExistingLink As Variant
Sub Whatever()
myFilePath = Application.GetOpenFilename()
If myFilePath = "False" Then
Sheet1.Range("M2").Value = ""
Else
Sheet1.Range("M2").Value = myFilePath
ExistingLink = ActiveWorkbook.LinkSources(xlExcelLinks)
'Sheet1.Range("EX10") = ExistingLink Testing whether the correct link path is getting pulled
ActiveWorkbook.ChangeLink Name:=ExistingLink, NewName:=myFilePath, Type:=xlExcelLinks
End If
End Sub