29461

In Excel, how can I programmatically edit the address in a range of cells containing hyperlinks?

Question:

I have a column containing hyperlinks and I want to do a programmatic search and replace of the hyperlink address - the address itself, not the text to display. How do I do this?

Answer1:

By modifying the "Address" property of the Hyperlinks element of a range object. Hyperlinks is a collection, so you will pick the first item if your range is a single cell, but it allows you to easily loop through all the links contained in a given range or worksheet.

Here's an example to change the link of the currently selected cell only (note: it must already have an hyperlink).

Sub ChangeLink() Application.ActiveCell.Hyperlinks(1).Address = "www.test.com" End Sub

Here's a variation that changes all links in the current worksheet to www.google.com, as long as they are not currently pointing to www.google.com (not necessary, but just for demonstration's sake):

Sub ChangeLinks() For x = 1 To Application.ActiveSheet.Hyperlinks.Count If Application.ActiveSheet.Hyperlinks(x).Address <> "www.google.com" Then Application.ActiveSheet.Hyperlinks(x).Address = "www.google.com" End If Next x End Sub

You can also add and delete links using the Hyperlinks.Add and .Delete methods. The TextToDisplay property allows you to change the text, and the Range property allows you to access (or figure out) which cells a given link belongs to.

EDIT: As requested, here's how to do it on all links in one specific column.

Most often I like to create a few variables to assign (Set) the objects I'm working with, such as the worksheet (calling "Sheet1" here), then the actual column which I will refer to by myColumn. If you get an error message, this allows you to pinpoint where it fails exactly.

Notice how the Hyperlinks collection adjusts itself nicely according to the object you call it from!

I also added a check that shows a message if there are no links in the specified column.

Sub ChangeLinksInColumn() Dim mySheet As Worksheet Dim myColumn As Range Set mySheet = Application.Worksheets("Sheet1") Set myColumn = mySheet.Columns(3) ' Enter column number here ' Check if column contains hyperlinks If myColumn.Hyperlinks.Count = 0 Then MsgBox "No hyperlinks in specified column." Else ' Perform task in myColumn only For x = 1 To myColumn.Hyperlinks.Count myColumn.Hyperlinks(x).Address = "www.google.com" Next x End If End Sub

Recommend

  • VBA - syntax for .pictures.insert incorrect? Insert method of picture class failed
  • How can I switch iframe using selenium vba?
  • Excel - Macro to create chart based on selection
  • Excel VBA - Get corresponding Range for Button interface object
  • Creating an array variable with a variable number of elements
  • Bloomberg Data… Why doesn't Application.WorksheetFunction.BDH work?
  • What's a robust method in R for importing from and exporting data to Excel?
  • Unable to delete a worksheet using EPPlus
  • How to read empty cells in PHPExcel without skipping values?
  • Formula in Excel that references another Excel file based on cell reference
  • Applying CIFiler to a masked portion of an image
  • How can the java 'class' literal return different instances of the Class object for the sa
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • Pattern combining type test and literal
  • VBA vlookup with defined range and file from other workbook
  • VB.NET - RichTextBox - Apply formatting to selected text
  • How do I pass worksheet and ranges as variables?
  • Stored Procedure with multiple IN Parameter
  • Why would a Scala Worksheet using Scala-IDE give this as an error?
  • Imports in __init__.py and `import as` statement
  • Can I use worksheet_change for a specific column only?
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Jquery UI tool tip close icon
  • Deselecting radio buttons while keeping the View Model in synch
  • Optimizing database types to compact database (SQLite)
  • C# - Serializing and deserializing static member
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • Cross-Platform Protobuf Serialization
  • Sending data from AppleScript to FileMaker records
  • JSON with duplicate key names losing information when parsed
  • Do I've to free mysql result after storing it?
  • Jquery - Jquery Wysiwyg return html as a string
  • Calling of Constructors in a Java
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • How to include full .NET prerequisite for Wix Burn installer
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Turn off referential integrity in Derby? is it possible?
  • Qt: Run a script BEFORE make
  • Is it possible to post an object from jquery to bottle.py?