Pretty often at my job I’m asked to create an ad-hoc report in Excel that grabs some info from our website’s database and presents it with whatever metric my manager asks for. The data will have information that can be linked to a specific page on the website and more often than not I’m asked to link the info to the page so if they want to look at a particular metric in more detail, they can quickly go to the site where the information is. So in this post, we’ll look at an easy way to dynamically create a hyperlink using info from an external source. For our example, let’s use the pubs database. We’ll get some information about book titles and sales. Here is our query:
SELECT t.title_id , t.title , a.au_lname , t.price , t.ytd_sales FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id
The data looks like this:
If you need help downloading the pubs database, click here.
For the sake of argument, let’s also say that I have a website called www.pubsdb.com (please don’t actually try to go there! It’s not a real location on the web!) and that there is a section where if I know the title_id, I can create a link to the book that it’s connected to. The URL would look like:
Quick TipA URL is the full name of the site. For example, www.google.com is the URL to get to Google’s website. In our example, notice the ?title_id=BU1032 portion. This is a parameter, much like the ones we use in Excel formulas, where it tells the website certain information about how the page should load. In this example, it is going to the website and telling it “hey, here’s the title_id of a book I want to look at” and the website responds accordingly.
Notice that I can use the
title_id as part of the URL and it will send me to the book in question. So we can use this data, along with the knowledge of how to build the URL based on
title_id and we can create dynamic hyperlinks.
Connect the Query to Excel
We’re going to put this query in an Excel table. If you’re not familiar on how to do this, please see how to use a SQL Query to create a PivotTable. The only difference here is when the Import Data dialogue comes up, you want to select Table instead of PivotTable.
The Excel table comes back like this:
OK, so we have the query that we can refresh whenever new data is entered into the database, and we have it shown as an Excel table so we can reference the data. This is where the
Hyperlink() function comes into play.
A quick note about how the
Hyperlink() function works. It’s pretty simple. Here is how the formula looks along with the parameters listed and what they mean.
|link_location||The URL where the link is pointing to. This can be dynamically built by concatenating strings together.|
|[friendly_name]||This is an optional parameter that will be the text displayed for the link. So instead of seeing a URL, you'll see whatever name you decide to give it.|
Building the Hyperlinks
For our example, we’re going to use the
title_id to create our hyperlinks. We start by adding another column to our Excel table. We’ll name the column Link and put it in
Notice that the column is formatted just like our Excel table. With Excel tables, we can add columns to external data and Excel will realize that we want it to look like it’s part of the data (which we do). You can even add/remove columns in your SQL query and Excel will go right along with it and not overwrite your data. Awesome 🙂
Back to the formula. We can now create the hyperlinks by starting in
F2. The formula is:
Notice that I manually put the part of the URL that we know what it needs to be and we stop right after the equal sign so we can tack on the
A2. When Excel evaluates the function, it will concatenate the static text plus the text found in
A2. For the friendly name, we’re putting the actual title. Now the title looks clickable, which it is. Now the table looks like this:
(by the way, I really don’t like shrinking the image like this, but I want to illustrate this to you)
Since we don’t need the
title_id and the old, non-clickable,
title column anymore, we can hide them and put the new
Link column in its place.
- Right-click on the Link column and select “Cut”
- Right-click on column
Cand select “Insert Cut Cells”
- Left-click and drag from columns
B, then let go of the left-click. This will select columns
Btogether. Right-click either column
Band select “Hide”
The table now looks like this:
And there you have it. Clickable links from external data. I hope this helps you in any future project you may have!
To the More Experienced People
I’d like to make a special note to the more experience people about this method of creating hyperlinks using external data. You may have been thinking I was going to have the query build the hyperlink and then have Excel realize that there is a URL there. I’m sorry to say that I have not found a way to easily do this.
I could have built the URL inside the SQL query itself, but then Excel would just see it as text. Even if you go that route, you can click inside the Excel formula bar (or hit F2) and press Enter and it will create a hyperlink at that point. However, every time you refresh the table it will just become plane old text again (except it still has the format of what looks like a hyperlink).
But seriously, do you really want to create a special VBA method that refreshes the data (or even captures a TableUpdate event) and then process all of the hyperlinks again? Or would you rather add a few extra columns, add a simple formula and let Excel handle the rest?