Home	Excel	Access
\| 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

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.

Get this information as a document
accompanied by Excel worksheets

Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlhtmltable.htm

Last updated Feb14

CMIDX S5 P2