Suppose you have a list of data with the same hyperlinks. With VBA code, you can quickly replace the old file path or address with a new one. Here’s how to do so: <\/p>\n
1. Press the ALT + F11 keys, to open the Microsoft Visual Basic for Applications Window.<\/p>\n
2. Click Insert > Module, and paste the following macro in the Module window.<\/p>\n
3. Paste the following script.<\/p>\n
\r\nSub FixHyperlinks()\r\n Dim wks As Worksheet\r\n Dim hl As Hyperlink\r\n Dim sOld As String\r\n Dim sNew As String\r\n\r\n Set wks = ActiveSheet\r\n sOld = "URL"\r\n sNew = "NEW URL"\r\n For Each hl In wks.Hyperlinks\r\n hl.Address = Replace(hl.Address, sOld, sNew)\r\n Next hl\r\nEnd Sub\r\n\r\n<\/pre>\nNote: be sure to add your own URLs for sOld and SNew.<\/strong><\/p>\n
4. Press F5 to run the macro.<\/p>\n","protected":false},"excerpt":{"rendered":"
Suppose you have a list of data with the same hyperlinks. With VBA code, you can quickly replace the old file path or address with a new one. Here’s how to do so: 1. Press the ALT + F11 keys, to open the Microsoft Visual Basic for Applications Window. 2. Click Insert > Module, and…<\/p>\n