Engineering the Digital Civilization

Search Here...

Saturday, 1 February 2020

Hyperlinking Macro for creating a dynamic linking index page on Excel that works in PDF

I wanted to create a table of contents for a spreadsheet report. The TOC is supposed to have clickable hyperlinks that navigate the view to the topic that is clicked. 

Since the number of rows and columns in the report changes frequently I had to keep it dynamic such that it automatically updates to the new position of the topic. I tried using the hyperlink function of excel but the problem with this approach is that the links get disabled when the spreadsheet is exported to PDF. There is a difference when the function is used and when the hyperlink is inserted using from the insert tab. I learned that somehow the hyperlinks created from the insert toolbar work in PDF while the hyperlink function gets disabled. 

Now we can use the insert toolbar to create hyperlink but this is a static form of application and one needs to manually update the hyperlink locations whenever the report is updated. 

So this action is required to be done manually every time but we can code a macro and automate much of the task. Here's how I did that:

1st I used the macro recorder to see what the insert hyperlink does at the back end of the software. 

It uses the following VBA code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!A1", TextToDisplay:="Link 1"

Then I tried using the same code for some other cells with some other reference and with a few trials and errors I was able to figure out how the syntax works. I then created a VBA to automate the task. Since there were a number of topics that were to be hyperlinked I I had to use the looping. The code used in the application is as follows 
Getting this task automated was a great adventure and I would like to thank Shabbir for helping me getting through the looping and Leila for the Unlock Excel VBA and Macros course. 

You can download the file from this link here

There are a few bugs in the macro and it fails at a few instances due to limitations of range. I'd appreciate your comments on the improvement of code, feel free to comment with suggestions!

Thanks for reading. 

No comments:

Post a comment