Rename File from Hyperlink on a Sheet in Excel VBA

[BadDogTitan]

I have a sheet which lists many relative hyperlinks. The 'text to display' is the filename.

Using the 'Worksheet_Change' event to monitor, if a user changes the filename in the cell the actual filename should be renamed to the new value, so the user doesn't have to open the folder and manually rename the file.

Any thoughts?

Thanks!

 

[John_w]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim link As Hyperlink
    Dim linkAddress As String, linkTextToDisplay As String
    Dim newLinkAddress As String
    
    If Target.Count = 1 And Target.Hyperlinks.Count = 1 Then
    
        Set link = Target.Hyperlinks(1)
        
        If link.Name <> link.TextToDisplay And link.SubAddress = "" Then
                    
            linkAddress = link.Address
            linkTextToDisplay = link.TextToDisplay
            newLinkAddress = Left(linkAddress, InStrRev(linkAddress, "\")) & linkTextToDisplay
            
            MsgBox linkAddress & vbCrLf & vbCrLf & _
                "will be renamed as" & vbCrLf & vbCrLf & _
                newLinkAddress
            
            Name linkAddress As newLinkAddress
            
            link.Delete
            Target.Hyperlinks.Add Target, newLinkAddress, , , linkTextToDisplay
            
        End If
        
    End If
    
End Sub

 

[BadDogTitan]

Works great John_w! Thanks for the quick response, too.

 

[BadDogTitan]

An issue has come up, John_W - if the user enters a space, a 'Run-time error '58': File already exists' occurs.

Also, if the user presses the 'Delete' key or clears the contents, it would be ideal if the user were prompted to delete the file.

 

[BadDogTitan]

Another issue with John_W's code, is that it works great until the user saves the workbook, then I get a file not found error. I know this has something to do with the links being relative, but I don't know how to fix it.

 

[John_w]

An issue has come up, John_W - if the user enters a space, a 'Run-time error '58': File already exists' occurs.

Also, if the user presses the 'Delete' key or clears the contents, it would be ideal if the user were prompted to delete the file.

This revised code should fix the error when the user enters a space (which results in a space hyperlink). The code now deletes this space hyperlink.

It is not really possible to handle the user deleting/clearing the cell contents, because in this case, when Worksheet_Change is called, the hyperlink no longer exists, so the code doesn't know which file should be deleted.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim link As Hyperlink
    Dim linkAddress As String, linkTextToDisplay As String
    Dim newLinkAddress As String
    
    If Target.Count = 1 And Target.Hyperlinks.Count = 1 Then
    
        Set link = Target.Hyperlinks(1)
        
        If link.Name <> link.TextToDisplay And link.SubAddress = "" Then
                    
            linkAddress = link.Address
            linkTextToDisplay = link.TextToDisplay
            
            If Trim(linkTextToDisplay) <> "" Then
            
                newLinkAddress = Left(linkAddress, InStrRev(linkAddress, "\")) & linkTextToDisplay
            
                MsgBox linkAddress & vbCrLf & vbCrLf & _
                    "will be renamed as" & vbCrLf & vbCrLf & _
                    newLinkAddress
                Name linkAddress As newLinkAddress
                
                link.Delete
                Target.Hyperlinks.Add Target, newLinkAddress, , , linkTextToDisplay
                
                Set link = Target.Hyperlinks(1)
            
            Else
            
                'User has entered a space - delete the link
                
                link.Delete
            
            End If
            
        End If
        
    End If
    
End Sub

Another issue with John_W's code, is that it works great until the user saves the workbook, then I get a file not found error. I know this has something to do with the links being relative, but I don't know how to fix it.

I can't reproduce this.

 

[BadDogTitan]

This is what is happening on my end. Before a save, everything works perfectly - as shown by the message box:

https://1drv.ms/u/s!AtBqEnIwxcu4guFVrgHN9yLyC79geA

After a save, the file is renamed however, it is moved from its original location to the same path where the workbook is saved.

After a save and reopen of the workbook, I get the following message box and the file not found error (note the path names now):

https://1drv.ms/u/s!AtBqEnIwxcu4guFWo8hlwI8QHQ6zKw

https://1drv.ms/u/s!AtBqEnIwxcu4guFY6vb5muVrBmIijQ

In my internet travels, I have figured out it has something to do with excel saving links as relative addresses, but the solution escapes me.

 

[John_w]

After a save, the file is renamed however, it is moved from its original location to the same path where the workbook is saved.

If you move the file to another folder Excel (and the code) does not know that the file has moved so it's impossible for it to update the link to the correct folder.

 

[BadDogTitan]

The file is being moved when it is renamed to the same path that the workbook is stored in. I don't know how.