none
Find address and relink address for a shape hyperlink in excel using code RRS feed

  • Question

  • I decided to step back and rethink what I need to make this work. Instead of trying to find new date and Rev #, Is there a way to get the hyperlink to find and accept the new address no matter what it says after the word "sheet".?

    Friday, October 28, 2016 11:46 AM

All replies

  • Hi,

    It is possible but it is unreasonable. Excel could not figure out which one it should follow if it is linked to non-unique file.

    For your last thread, my sample code doesn’t work for you because your hyperlinks are linked to folders instead of files based on the screenshot. If so, we could not follow them.

    I suggest you get the hyperlinks and check if the old address and new address exist. If it exists, we update. You could refer to Asadulla's code or use Scripting.FileSystemObject.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 31, 2016 5:10 AM
    Moderator
  • Asadulla Javed,

    I ran your code you gave me from a previous thread, I'm getting a runtime error '-2147467259(80004005)' Method 'TextToDisplay' Object 'Hyperlink' failed. Could you possible take another look at this? Up top is where the folder and files are located (example). Do you think it is possible to find the new address by the code you have made for me by getting it to search for anything after the "sheet" part of the address.?

    Thanks

    Monday, October 31, 2016 2:06 PM
  • Hi,

    You could test the following code and you need to change the newAddress based on your rule.

    Sub Demo() Dim h As Hyperlink Dim oldaddress, newAddress As String For Each h In Worksheets("Sheet1").Hyperlinks oldaddress = h.Address If Not checkFolderExist(oldaddress) Then newAddress = "" If checkFolderExist(newAddress) Then h.Address = newAddress MsgBox "It has been updated" Else MsgBox "Update failed, please check if the new path exist" End If End If Next End Sub

    Function checkFolderExist(ByVal folderPath As String) As Boolean
    Set fso = CreateObject("Scripting.FileSystemObject")
    If (fso.FolderExists(folderPath)) Then
    checkFolderExist = True
    Else
    checkFolderExist = False
    End If
    End Function

    Besides, I suggest you share with us the file which its hyperlinks need to update. You could upload into OneDrive and share the link here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 1, 2016 6:49 AM
    Moderator