Creating a list of hyperlinks from table data

[BlissC]

In my current project I'm trying to a get a list of hyperlinks on an Excel sheet from a list of URLs and "friendly names" in a table on another sheet. For context, the workbook is a leave request and log system that each employee has their own copy of. On the workbook's 'Welcome' page I'm trying to get a list of links to leave policies and procedures on the company intranet. As documents have a tendency to get moved around, updated or deleted, I don't fancy having to update each of the 200+ users' workbooks every time a link breaks! I already have a central Excel workbook that holds data for data validation/drop-down lists, etc. that the workbook grabs data from, which is imported into a sheet in the workbook using "Queries & Connections">Refresh All (using Excel 2019/365).

I've added two columns to the data table - "PolicyLinks" which holds the URLs to articles on the intranet/policy documents, and "PolicyLinkName" which holds the "friendly name" for each link. The attached image shows the setup of my data table and how I'm envisaging the list of links looking on the 'Welcome' page. There may be fewer links than shown, or there may be more - it depends on how many articles the HR dept has on the intranet. I need to be able to simply add the details of new links to my data table, or remove out-of-date ones, which will update the users' workbooks automatically when they refresh their data when instructed. I know how to use the HYPERLINK function, but when it comes to how to get them onto the 'Welcome' page without hard-coding them on the 'Welcome' page, my mind's gone blank. I suspect it's something very simple, but I can't get my head round it. Any assistance gratefully received.

Thanks,

Bliss

 

[Scott T]

If you are OK with VBA then something like this would work. You will need to change to fit your data

Sub hyplink()

Dim lr As Long
Dim nr As Long
lr = Cells(Rows.Count, "W").End(xlUp).Row
nr = Cells(Rows.Count, "A").End(xlUp).Row + 1

For x = 2 To lr

With Worksheets(1)
 .Hyperlinks.Add Anchor:=.Range("A" & nr), _
 Address:=Range("W" & x), _
 TextToDisplay:=Range("X" & x).Text
End With
nr = Cells(Rows.Count, "A").End(xlUp).Row + 1

Next x

End Sub

 

[BlissC]

Thank you Scott for your reply. I'm very much new to VBA (my level of VBA knowledge is "copy, paste and change the odd bit") and after some head scratching and looking back at my original post I realised that 'W' and 'X' are the columns where the data is, but I'm not sure what 'A' refers to.

I'm also not sure where I should put this (ThisWorkbook, the bit for the sheet I want the to links on (sorry, I don't know what the list of sheets in the project window are called), or as a module), and how I refer to the VBA on the worksheet where I want the links to display.

Sorry for all the questions.

 

[Scott T]

'A' is referring to where the hyperlinks will be placed. You will need to change to code to refer to the sheet and range that match your data. The code posted just uses one sheet. see the explanations in green. You will need to adjust the cells and what sheet the cells are one to fit your data. where this goes depends on how you want to run the code. If you want to manually run the code for example a button on the sheet then it can go into a module if you want it to be triggered by an event then it would need to go into the sheet or ThisWorkbook object depending on what action triggers the code.

Sub hyplink()

Dim lr As Long
Dim nr As Long
lr = Cells(Rows.Count, "W").End(xlUp).Row
nr = Cells(Rows.Count, "A").End(xlUp).Row + 1
'This creates 2 variables lr and nr. lr is the last row in your list of hyperlinks and nr keeps track of the next row of where you want to put the hyperlink for example if you have a hyperlink in row 2 then nr=3.

For x = 2 To lr
'this starts a loop so the code look at all rows in your list of hyperlinks starting from row 2 to the last row.

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("A" & nr), _
Address:=Range("W" & x), _
TextToDisplay:=Range("X" & x).Text
End With
'this is what creates the hyperlink in the cell with the "friendly" name

nr = Cells(Rows.Count, "A").End(xlUp).Row + 1
'since the next row has changed this gets the new next row

Next x
'since we are finished with that row this goes back to the start of the loop where x is incremented by 1 the code within the loop is run again if it has not reached the end.

End Sub

 

[BlissC]

Thanks Scott, that's really helpful.