MeadInKent
| Feedback | Advanced HTML Macro |

Creating Web Pages using an Excel macro (simple version)

Excel contains an option to save a worksheet as an HTML file ([File] Save as | File type HTML). These generated pages are difficult to customise and can include unnecessary elements such as macro buttons and text boxes converted to images. The Microsoft style codes can contain non-standard instructions which are not understood by all browsers. If you have a minimal knowledge of HTML coding and can recognise the workings of a simple VB macro, the following code may offer a better solution.

- A B C D
1 Staff List      
2        
3 Surname Joining Date Salary Tax rate
4 Brown 12/03/2001 12456 0.2
5 Dunton 12/01/1989 23456 0.25
6 Elgar 01/06/1995 32136 0.25

This Excel macro should be run from a worksheet containing a table you wish to save as a web page. There are various elements of code which need to be modified to reflect your individual table, such as the range of cells (FirstRow, LastCol etc) and a formatting style for each column for text, numbers or dates.

When run, the macro creates a text file containing all of the HTML code necessary for your page.

I have also produced a more complex alternative macro which generates a formatted web page from a worksheet.

Copy this code into a new macro module:

Option Base 1 ' sets first array element to 1, not 0
Sub MakeHTM_Basic()
' Defining a list of variables used in this program
Dim PageName As String, FirstRow As Integer, LastRow As Integer
Dim FirstCol As Integer, LastCol As Integer, MyBold As Byte
Dim TempStr As String, MyRow As Integer, MyCol As Integer
Dim MyFormats As Variant, Vtype As Integer, MyPageTitle As String

' MyFormats is an array which can contain formats for numbers
' and dates. Add one element for each table column.

MyFormats = Array("", "dd mmm yy", "£#,##0", "0%")

PageName = "d:\tempm.htm" 'location and name of saved file
MyPageTitle = Range("A1").Value

FirstRow = 3 ' the range of the worksheet to be
LastRow = 6 ' converted into an HTML table
FirstCol = 1
LastCol = 4

If UBound(MyFormats) < (LastCol - FirstCol + 1) Then
 MsgBox "The 'MyFormats' array has insufficient elements", vbOKOnly + vbCritical, "MakeHTM macro"
 Exit Sub
End If

Open PageName For Output As #1
Print #1, "<html>"
Print #1, "<head>"
Print #1, "<title>Excel to HTML simple table [MeadInKent]</title>"
Print #1, "<style type='text/css'>"
Print #1, "body {font-family: Arial, Helvetica; font-size: 11pt; margin-left: 10; margin-right: 10}"
Print #1, "td {padding: 1pt 3pt 2pt 3pt; border-style: solid; border-width: 1; border-color: #0F5BB9; font-size: 11pt}"
Print #1, "table {border-collapse: collapse; border-width: 1 ; border-style: solid; border-color: #0F5BB9 }"
Print #1, "</style>"

' The next line refers to a cascading style sheet as an alternative to the <style> instructions
' Print #1, "<link rel='stylesheet' type='text/css' href='mikbasic.css'>"


Print #1, "</head>"
Print #1, "<body>"
Print #1, "<h1>" & MyPageTitle & "</h1>"
Print #1, "<table>"
For MyRow = FirstRow To LastRow
Print #1, "<tr>"

For MyCol = FirstCol To LastCol
 If Cells(MyRow, MyCol).Font.Bold = True Then
 MyBold = 1
Else
 MyBold = 0
End If

Vtype = 0 ' check whether the cell is numeric
If IsNumeric(Cells(MyRow, MyCol).Value) Then Vtype = 1
If IsDate(Cells(MyRow, MyCol).Value) Then Vtype = 2

' if numeric and a format code has been created, apply it
If Vtype > 0 And MyFormats(MyCol - FirstCol + 1) <> "" Then
 TempStr = Format(Cells(MyRow, MyCol).Value, MyFormats(MyCol - FirstCol + 1))
Else
 TempStr = Cells(MyRow, MyCol).Value
End If

If MyBold = 1 Then
 TempStr = "<b>" & TempStr & "</b>"
End If

If Vtype = 1 Then ' align numbers (not dates) to the right
 TempStr = "<td align='right'>" & TempStr & "</td>"
Else
 TempStr = "<td>" & TempStr & "</td>"
End If

' if a table cell is blank, add a space
If TempStr = " <td></td>" Or TempStr = "<td align='right'></td>" Then
 TempStr = " <td>&nbsp;</td>"
End If

Print #1, TempStr
Next MyCol
Print #1, "</tr>"
Next MyRow

Print #1, "</table>"
Print #1, "<p>You can search for a name or any detail using [ctrl]+'f'. Press [Home] to<br>"
Print #1, "move to the top of the page. It can be copied and pasted into Excel</p>"
Print #1, "<hr>"
Print #1, "<p><small>Source file: " & ThisWorkbook.Name & " | Page name: " & PageName & " | Created: " & Format(Date, "dd mmm yy") & " | www.MeadInKent.co.uk</small></p>"
Print #1, "</body>"
Print #1, "</html>"
Close #1
MsgBox "The file has been saved as " & PageName, vbOKOnly + vbInformation, "MakeHTML macro"
End Sub

When the text file is opened using your web browser, the resulting page should look something like the following:


Create web page tables with expanding or drill down sections

access and html

Find out more

Staff List

Surname Joining Date Salary Tax rate
Brown 12 Mar 01 £12,456 20%
Dunton 12 Jan 89 £23,456 25%
Elgar 01 Jun 95 £32,136 25%

You can search for a name or any detail using [ctrl]+'f'. Press [Home] to
move to the top of the page. It can be copied and pasted into Excel


Source file: MakeHTML demo.xls | Page name: d:\tempm.htm | Created: 03 Nov 07 | MeadInKent.co.uk

Note that to get an 'attractive' table, the HTML code should include some formatting instructions - either by defining some styles or by linking to an external Cascading Style Sheet . Neither of these options are essential however.

This macro makes a lot of sense when creating large tables because the generated code is less than the equivalent Excel menu option.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlhtmltable.htm © MeadInKent.co.uk 2016 Last updated Feb14 CMIDX S5 P2