Change Hyperlinks

[something68]

I moved excel spreadsheets from OneDrive to S:Drive and I want to change their hyperlinks on an excel sheet that I have in U:Drive. How do I change the hyperlinks all at once?

 

[LegendaryAxe]

Provided all the hyperlinks are the same, try this:

Sub ChangeHyperlinks()

Dim Cell As Range
Dim ws As Worksheet
Dim hl As Hyperlink

For Each ws In ThisWorkbook.Worksheets
    For Each Cell In ws.UsedRange
        If Cell.Hyperlinks.Count > 0 Then
            For Each hl In Cell.Hyperlinks
                If hl.Address = "C:\Users\AAA\OneDrive\Documents" Then ' insert old address
                    hl.Address = "U:\" 'insert new address
                End If
            Next hl
        End If
    Next Cell
Next ws

End Sub

 

[something68]

Provided all the hyperlinks are the same, try this:

Sub ChangeHyperlinks()

Dim Cell As Range
Dim ws As Worksheet
Dim hl As Hyperlink

For Each ws In ThisWorkbook.Worksheets
    For Each Cell In ws.UsedRange
        If Cell.Hyperlinks.Count > 0 Then
            For Each hl In Cell.Hyperlinks
                If hl.Address = "C:\Users\AAA\OneDrive\Documents" Then ' insert old address
                    hl.Address = "U:\" 'insert new address
                End If
            Next hl
        End If
    Next Cell
Next ws

End Sub

 

[something68]

I have no idea what to change, etc. on what you provided so I will simply change the link to each name as I work on their worksheet.

Thanks.

 

[LegendaryAxe]

You would just need to change the folder addresses in quotes in the following bit of code. The first line you put the address (the dummy address is in quotes) you currently have in the workbook, and the second line is the address (the dummy address is in quotes) you want to replace it with. Again, this is only if all the hyperlinks are the same. If they're not, describe exactly what you changed with the existing hyperlinks.

                If hl.Address = "C:\Users\AAA\OneDrive\Documents" Then ' insert old address
                    hl.Address = "U:\" 'insert new address

 

[something68]

This is still not helpful to me and still confusing. I tried it and I'm feeling it's taking more time to try things than it is to change them as I work on them. I recall someone I work with helped me before but they transferred and I don't know if I'd be able to remember what he helped me with for him to help again.

Thank you.

 

[Joe4]

Are you simply wanting to change the drive letter from "S" to "U" and keep everything else the same?

 

[something68]

I have a list of employees on the U:Drive. On the S:Drive, I have their Record of Absence. I would like to be on the U:Drive and click on their name to bring up their "personal" ROA but it won't open since I moved from C:Drive (then deleted from) to S:Drive.

I do not understand that VBA code and I also noticed that the option to edit links in the data tab is not available. I thought I'd be able to click and highlight the column of employee names to change/update the drive.

 

[Joe4]

I am not quite sure I understand that you are saying.

Can you just show us how the hyperlinks need to be updated, in plain English?
Maybe show us an example?

 

[something68]

And where do I find the hyperlinks? See, this is turning into way too much work. I will just update the links individually as I work on them. It will take a bit longer, but it's the only other way.

Thank you.