Generating Hyperlink Lists

Back last year, I was producing some HTML user documentation for a financial system. The pages were grouped into folders by type: functional modules, panels (screens or screen sections), general UI facilities, and so on. The contents of each of the folders grew as content was added. What I wanted was a way to generate an HTML page in each folder that presented a list of hyperlinks to each file in that folder. For example:

ui-list

The first link points to the file ‘UI Filter Fields.html’. “UI ” is the prefix; “.html” is the suffix.

To generate these hyperlinks, my thoughts turned to Excel’s HYPERLINK function. What I needed was a workbook that would put the ‘prefix *.html’ files into a column, then strip off the prefix and suffix, and then use HYPERLINK to create a column of links. The workbook would then sort the hyperlink column and publish it as a web page. Simple!

First, the Excel side. The main ‘list’ worksheet looks like this:

list_xls

Column A (range “filenames”) is populated by VBA; columns B and C (range “linknames”) strip off the prefix and suffix (using the SUBSTITUTE function); column D contains =HYPERLINK(filenames,linknames). Columns A to C could be hidden if you wish.

A ‘settings’ worksheet allows prefix and suffix to be set, returns the number of files found, and has the Run button:

settings_xls3

Now the VBA. The Run button kicks off the whole process:

Private Sub RunButton_Click()
    GetFilenames
    Sort
    Save
End Sub

GetFileNames uses the VBA Dir function to return matching file names in sequence. There are put into column A. Notice that Dir is called repeatedly until it returns an empty string.

Sub GetFilenames()
        'pre: exists Worksheets("list")
        'pre: defined Range "filenames" = $A:$A
        'pre: defined Ranges "prefix", "suffix"
        'post: Range "filenames" contains
        '               zero or more matching file names

    Dim prefix As String
    Dim suffix As String
    Dim filename As String
    Dim rownum As Integer

    ChDir (ActiveWorkbook.Path)

    Worksheets("list").Activate
    Range("filenames").Clear

    prefix = Range("prefix").Value
    suffix = Range("suffix").Value

    rownum = 1
    filename = Dir(prefix + "*" + suffix)

    Do While filename <> ""
        Range("A" & rownum).Value = filename
        rownum = rownum + 1
        filename = Dir
    Loop

End Sub

The data region is then selected and sorted ascending on column D (which is textually the same as column C).

Sub Sort()
        'pre: exists Worksheets("list")
        'pre: defined Range "filecount"
        '                 contains =COUNTA(list!$A:$A)
        'post: Worksheets("list") is sorted on column D

    Dim filecount As Integer
    Dim rng As String

    Worksheets("list").Activate

    filecount = Range("filecount").Value
    rng = "A1:D" & filecount
    Range(rng).Select

    Selection.Sort _
            Key1:=Range("D1"), _
            Order1:=xlAscending, _
            Header:=xlNo, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom
End Sub

Finally, the hyperlinks in column D are selected and published as a web page, to a file with a name generated using the prefix and suffix.

Sub Save()
        'pre: exists Worksheets("list")
        'pre: defined Range "filecount"
        '                contains =COUNTA(list!$A:$A)
        'post: exists File .\out prefix List.suffix

    Dim filecount As Integer
    Dim prefix As String
    Dim suffix As String
    Dim rng As String
    Dim title As String
    Dim outfile As String

    filecount = Range("filecount").Value
    rng = "list!D1:D" & filecount

    prefix = Range("prefix").Value
    suffix = Range("suffix").Value
    title = prefix & "List"
    outfile = ".\out " + title + suffix

    ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            filename:=outfile, _
            Sheet:="list", _
            Source:=rng, _
            HtmlType:=xlHtmlStatic, _
            title:=title).Publish True

End Sub

Both the sort and publish operations are the kind of thing that you initially do manually with the Macro recorder on, to get some base code with the right arguments and options.

This works for HTML files, and also ‘.doc’ and ‘.xls’ files; just set the suffix appropriately. In these latter cases the output file is still HTML, but with a ‘.doc’ or’.xls’ suffix; Word and Excel are quite happy to open these. You could, if you wish, have an input suffix and an output suffix, but this isn’t necessary.

I think there’s potential for using VBA plus the HYPERLINK function in other ways, to generate ‘dynamic links’.

The main VBA module is available from my website.

Advertisements

2 Responses to “Generating Hyperlink Lists”


  1. 1 Simonn March 22, 2009 at 4:51 am

    Very interesting article, i have bookmarked your blog for future referrence. Best regards

  2. 2 Kelvin Geis January 6, 2014 at 2:22 am

    I would like to better understand what all the code means. I need to code an excel spreadsheet so that it autogenerates hyperlinks to files in the same folder as the excel spreadsheet. I simply don’t know where to start.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




March 2009
M T W T F S S
« Feb   Apr »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

%d bloggers like this: