Title: Use VBA macros to add hyperlinks to external files

My book Essential Algorithms: A Practical Approach to Computer Algorithms has associated instructor materials that include lesson plans and PowerPoint presentations that are accessible via hyperlinks. The materials include a summary Excel workbook that contains hyperlinks that let you open the lesson plans and PowerPoint presentations. In the workbook, column C contains links to lesson plans (Word documents) and column D contains links to PowerPoint presentations. If you click the hyperlinks, the linked documents open.

Whenever I need to perform the same task a bunch of times, I try to write a program to do it. Then if I need to make changes or do the same thing again later, I don't need to do all the work by hand.

This example includes three macros for creating and deleting hyperlinks in an Excel workbook.

I entered the names of the Word documents in column C. The following VBA code uses those names to make hyperlinks in cells C2:C31.

' Make hyperlinks in cells C2:C31 where the name of the ' linked Word document is in the cell's contents. Sub WordLink() Dim link As String Dim i As Integer For i = 2 To 31 Range("C" & i).Select link = ActiveCell.FormulaR1C1 ActiveSheet.Hyperlinks.Add _ Anchor:=Selection, _ Address:=link, _ TextToDisplay:=link Next i End Sub

The code loops over the numbers i = 2 through 31. For each value i, the code selects the cell in row i column C and gets that cell's formula (its text).

The code then uses the ActiveSheet.Hyperlinks collection's Add method to create a new hyperlink. The Anchor parameter determines the cell that contains the hyperlink. The Address is the link's destination, in this case the name of the Word document. The TextToDisplay parameter tells what the cell should display.

The following code fills in the PowerPoint links in column D.

' Make hyperlinks in cells D2:D31. Get the Word document name ' in column C. Convert the name into a PowerPoint file name by ' replacing "Lesson" with "Chapter" and "docx" with "ppt." Sub PptLink() Dim link As String Dim i As Integer For i = 2 To 31 Range("C" & i).Select link = ActiveCell.FormulaR1C1 link = Replace(link, "Lesson", "Chapter") link = Replace(link, "docx", "ppt") ActiveSheet.Hyperlinks.Add _ Anchor:=Range("D" & i), _ Address:=link, _ TextToDisplay:=link Next i End Sub

This code loops through the numbers i = 2 through 31 again. For each i, the code gets the value in column C of row i. It makes some replacements to convert a Word document name such as Lesson04-02.docx into a PowerPoint presentation name such as Chapter04-02.ppt. It then creates the link as in the earlier code.

The final piece of code for this example removes the hyperlinks from the cells in a selection.

' Remove the hyperlinks from the selected cells. Sub RemoveHyperlinks() Dim cell As Range For Each cell In Selection.Cells cell.Hyperlinks.Delete Next cell End Sub

For each cell in the selection, the code calls cell.Hyperlinks.Delete to delete all of the links in the cell's Hyperlinks collection (if there are any).

Download the example to experiment with it and to see additional details.