Thanks for this thread ?? It helped me with a project for my local sports club – the idea was to maintain an Excel file of fixtures, whereby a match report (if written) was linked to each fixture, but they were generally by different authors.
However, not every match/fixture had a report so although I was able to use JGravesNBS macro, I had to modify it as otherwise it would generate HTML output for every single row. So what it does now (or should) is to skip to the next row if Column F is empty.
I have included the modified macro below the column structure for reference purposes, where ** are the hidden data columns:
Column A: Date
Column B: Home Team
Column C: Score
Column D: Score
Column E: Away Team
** Column F: Match Report (URL)
** Column G: Match Report (Author)
Column I: Match Report
Public Sub HTML()
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To LastRow
If Cells(I, 6).Value <> "" Then
Cells(I, 8).Value = "<a href=" & Chr(34) & Cells(I, 6).Value & Chr(34) & ">By " & Cells(I, 7).Value & "</a>"
End If
Next I
End Sub
All I needed to do then was to setup the table in TablePress to hide ** columns F and G from the public and from now on, the person who maintains the site just needs to export as csv and import to replace the table each time.
Hope this helps someone else ?? Thanks again to the peeps before for their contribution.
p/s: I thought about using Automatic URL Conversion, but space is a premium so long post urls alas would not have worked for this setup.
p/s2: I will probably do a full write up on my site at some point with a demo as I also used the Row Filtering extension. Tobias rocks ??
