|
|
|||
Excel contains an option to save a worksheet as an HTML file. These generated pages are often unattractive to look at and contain vast amounts of unnecessary code - making the web page slow to load. 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> </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:
Staff List
You can search for a name or any detail using [ctrl]+'f'. Press [Home] to 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 |
| file: xlhtmltable.htm | © meadinkent.co.uk 2007 | Last updated Nov07 |
