Excel to HTML conversions

Location: http://www.mvps.org/dmcritchie/excel/xl2html.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

XL2HTML and XL2HTMLX macros

Macro Code «  http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt  «  Macro Code
Instructions to install a macro are on my Getting Started with Macros page

The code referred to on this page are VBA macros.  If you need assistance to install macros please refer to Getting Started with Macros or for more depth on my Install Macros and User Defined Functions page.

The generated code will be about one third the size of the humongous code generated by Microsoft converters which attempt to make HTML look like an Excel page.  The primary purpose of XL2HTML is to generate a smaller file and let HTML do it's own thing rather than simulate Excel.

The XL2HTML and XL2HTMLx macros are really designed to create a smaller footprint for an HTML Table without all of the extra garbage, fonts, colors.  Quite the opposite of the conversions provided in Excel.  For tables not concerning numbers, HTML does a much better job at formatting and my objective is to let HTML do what it does best without interference of adding information I don't want:  exact cells, colors, formatting, excessive hyperlinks, etc. 

Conditional Formatting was not going to be originally included in the conversion but after several years it is available now, using some functions modified from Chip Pearson's original “Conditional Formatting Colors” (VBA coding) -- an example of generated results can be seen on my Conditional Formatting page.  Some CSS (Cascading Style Sheets) code made the addition of (black) borders possible so a fairly decent rendering in HTML beyond really simple tables has resulted; nevertheless, the purpose is to generate HTML code to be pasted in your own HTML coding, though you can, of course, change the heading material to generate a complete HTML page.

The Excel save as, save as webpage and HTML wizards on the other hand try to specifically make everything look the same between Excel, MS Word, and HTML.  Excel particularly generates excessive HTML coding in order to generate XML code, styles, and round-tripping where you can take the HTML and actually go back to the actual Excel from just the HTML and all its added auxiliary files which you may not see at first glance.

The macros described simply show that it is possible to write out your own HTML code using macros and you can modify macros to do more specifically what you really want to do.  I try to include a lot of the features but specifically leave out space robbing font and exact size things, especially since I think HTML browsers generally do a much better job left to their own devices.  I also purposely drop some but not all of the hyperlinks.  so if you want more or less you will have to modify coding to fit your requirements, unlike working with an add-in or a program that you have no control over.

Subroutines included in the code, each calls the generalized XL2HTML_Main

XL2HTML xl2html, convert selection to HTML,  converts selection on an Excel sheet to an HTML Table

The selection to be converted may comprise the entire page. 

XL2HTMLX (extended) xl2htmlx, convert selection to HTML, converts selection on an Excel sheet to an HTML Table, including row/col headers

The selection to be converted may comprise the entire page.  You will be able to see the shaded A-B-C column headings and the shaded 1-2-3-4 row headings.

XL2HTMLm, Multiple areas w/o headers based on marksepareas

Each area selected will generate a separate HTML Table.

XL2HTMLs, output multiple selected sheets

Each worksheet in a group of selected worksheets will generate a separate HTML Table.

A brief look into what HTML is, limitations, etc. (#HTML)

What these macros do

    'Original coding and concept is based 28 lines of code from
    'http://www.herber.de/mailing/020598v.txt   [archived copy]
    ' Hans W. Herber * Microsoft Excel MVP
    'Major changes D.McRitchie, 1998-08- msgbox, close,
    ' http://www.mvps.org/dmcritchie/excel/xl2htmlx.txt
    ' Shading option, Column & ROW headings and will use center justification
    ' when specifically formatted into Excel.
    'Additional help Invoking IExplorer from VBA -
    ' From: "Chris Rae" posted Excel.programming 9Jun1999
    'rev. 2000-06-25, hyperlinks for http:// (not email)
    ' Include  Bold, Italic, Color (black for email)
    'rev. 2000-07-01, handle merged cells (Rob Bruce),
    ' additional changes: center justification,
    ' multiple cells with TR "line", if fits in 80 bytes,
    ' XL2HTML_Main will be used by both XL2HTML and XL2HTMLx,
    ' center justification.   Rev. 2000-07-02 added 
for Chr(10) 'rev. 2000-07-03 for Center Across Selection (cell format) 'rev. 2001-08-11 multiple sheets, entire used content from each 'rev. 2001-08-11 for optional output dataset name in parameters 'rev. 2002-07-25 to right align numbers by default 'rev. 2002-08-24 to not right align empty cells 'rev. 2003-02-09 multiple areas (snake cols) see xl2HTMLa ' prob. not compatible with multiple sheets 'rev. 2003-09-07 TR align/bgcolor, TD left or right alignment space 'rev. 2003-09-07 Extra space for cells with A160, fix yesterday's alignment 'rev. 2003-09-25 TR align gets alignment required by majority in row 'rev. 2004-08-21 Change Shell invoking Internet Explorer 'rev. 2005-07-26 Conditional Formatting added:  font/interior color, bold, italic 'rev. 2005-12-30 addition of (black) borders with css see my font.htm page 'rev. 2006-03-12 provide for cells larger than 1024 chars (.value instead of .text) 'rev. 2006-07-05 correction if lastcell is a merged cell

Changes to Shell invoking Internet Explorer

Have had to change the coding invoking Internet Exploer as older version no longer work, note it is now “Explorer” intead of “IExplore”:
      Dim RC As Long
      'typical value of filename    c:\temp\xl2test.txt
      RC = Shell("Explorer " & filename, 1)
      'the following continues to work for notepad
      Shell "notepad " & filename 
      'the following is an Excel alternative
      ActiveWorkbook.FollowHyperlink(filename) 

Comparison of Generated File sizes (#comparison)

The file generated from XL2HTML is 57 KB with color fonts and background and limited fontsizing.  Compare to the Excel generated Save As HTML size of 217 KB with all of its round tripping code which preserves Excel's formulas etc.  You may examine the files and their generated HTML below.
 Sample filename size  filetype
 Original Excel File (sheet in color, sheet in B&W)  xl2html_filesizes.xls 109 KB  Excel Worksheet
 Generated from Excel save as HTML ***  xl2html_color.excel.htm 217 KB  HTML Document
 Generated using XL2HTML ***  xl2html_color_xl2html.htm 57 KB  HTML Document
 Generated using XL2HTML (no color)  xl2html_nocolor_xl2html.htm 36 KB HTML Document
 Generated using XL2HTMLx (headers/no color)  xl2html_nocolor_xl2htmlx.htm 48 KB HTML Document
 Generated using XL2HTMLx (headers)  xl2html_color_xl2htmlx.htm 69 KB HTML Document
 Pasted from Excel into FrontPage  excel_color_frontpage.htm  840 KB  HTML Document
 Edited XL2HTML output and saved in FrontPage   xl2html_color_frontpage.htm 57 KB  HTML Document
Interpretation of Table:
Starting with the color sheet of the Excel file, using XL2HTML macro you will get a 57 KB file, if you edit that 57 KB file in Front Page and save it you still have a 57 KB file, but if you paste from Excel into Font Page to create your HTML it becomes a whooping 840 KB HTML file (14.7 times a large as if you used XL2HTML).  If you would like to try your own tests these files are found in the folder http://www.mvps.org/dmcritchie/excel/xl2html/   -- Naturally a typical HTML would not simply consist of only a table.  I am using Excel 2000, and Front Page 2002 in the above.  The examples have merged cells involving both columns and rows and single color cell in a row and all cells in a row with same color to make things more interesting for conversions.

A conversion of the color sheet was attempted by Nvu which provided 234 KB file, which was a terrible rendition and lost merged cells, borders, and all color (view result).  In fairness you can't paste HTML table with Firefox as browser into Excel, you have to stick with Microsoft IE and Office Products for round tripping things.

Color, background and images not printing (#color)

Colors for Printing and Print Preview are controlled in IE from the setting under Internet Options, Advanced, “Printing”.

There is an option in Front Page that you would want to make sure you have turned on. Tools, Page Options, HTML Source (tab),  [x] Preserve existing HTML

If you do any editing with Front Page you want to keep an eye on the META tags in your HTML Headers, if they indicate Front Page as the generator then you know Front Page has done something more to your code than you probably wanted.  I rarely use FP to edit the code, it likes to stick in <DIV> and other things when you paste code.  I mostly uses NotePad (view source from IE), HTML-Kit (for syntax checking), and Front Page (for spelling).

Preparation of an Excel file for conversion using XL2HTML xl2html, convert selection to HTML,  or XL2HTMLX xl2htmlx, convert selection to HTML   (#preparation)

Macro Code   « http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt  « Macro Code
Instructions to install a macro are on my Getting Started with Macros page
XL2HTMLX was used in the above examples.  XL2HTML was used in the example for Shortcut keys for Excel 2000.  The macro « code is available here« for both macros.  See toolbars.htm for help in installing toolbar buttons   xl2html, convert selection to HTML  xl2htmlx, convert selection to HTML in support of these macros.
The main focus of this web page is on creating HTML tables to be inserted into your HTML coding and was described above.  Other HTML items continue.

Simple .BAT file to merge files

Was going to try to place coding into XL2HTML to embed some files and then realized there would be a problem incorporating code to include files in the very table that should be embedded. 

In the meantime while mulling this over a simple DOS .BAT file can be incorporated in a SHELL command using the same directory that you would output the HTML to from the Excel file. .

A simple .BAT file to copy 3 files into one output.

     c:
     cd c:\temp\billsweb
     copy part1.html +passlist.html +part3.html  composite.html
The above could be named PUSH.BAT and included in a macro as follows:
Sub push01()
  'Generate the HTML table, specifying
  Call XL2HTML_Main(0, "1T", "c:\temp\billsweb\passlist.html")
  'documented in http://wwww.mvps.org/dmcritchie/excel/xl2html.htm
  'push.bat  merges 3 files:  part1.html +passlist.html +part2.html into Composite.html
  Shell "c:\temp\billsweb\push.bat"
  Shell "notepad.exe c:\temp\billsweb\composite.html"
  RC = Shell("Explorer " & "c:\temp\billsweb\composite.html",1)
End Sub
The 1st option is without row/column headings, the 2nd option is 1 pass, and Timestamp, The 3rd option is the name of the output file overriding default.

The above "1T" will not generate HEAD, TITLE, BODY records, nor their closing tags, because file created from Excel will be placed in the middle of the merged files.


Material on Favorites, sorting bookmarks in favorites, exporting a bookmark file from favorites has been moved to Favorites.

Acknowledgments

My main interest in converting Excel to HTML was to generate efficient coding of an HTML Table.  Machine generated coding lacks such things as formatting the table or rows instead of cells to generate the smallest coding.  The Internet Assistant generates terrible HTML coding, overriding HTML defaults that make HTML so flexible; but does make the result look the same as the Excel original.  While I dislike and do not have the web space for the humongous code generated by Internet Assistant, I have ended up including many of the features that waste space -- fonts, color, links.  I do not include default fonts in the generation.  My coding has its basis in about 30 lines of code where Hans Herber (see Related below) showed us that it could be done and how simple it can be.  After being shown that macro code could generate an HTML table, and becoming more familiar with VBA, it was simply a matter of time before incorporating features in generation of HTML tables related to working on a mainframe creating and using macros to help generate DCF (Document Composition Language), and GML (Generalized Markup Language) documents.

HTML Specifications and Syntax & Link checking

(most have downloads available)

HTML Conversions, to and from (other than Excel)

HTML information on Microsoft sites

HTML information on non MS sites

HTML lists of links (#linklists)

HTML Things that look interesting

CSS - Cascading Style Sheets (#css)

JavaScript (#JavaScript)

XML references for possible future use (#xml)

Stripping out the Garbage from MS conversions to HTML   (#filter)

Eliminating the round tripping code, and font changes, especially those that are unnecessary and take up lots of space in coding.

File Input/Output

Output statement

To create in your Options, General, "default file location" directory. (untested)
  Open "Test.txt" For Output As #1

To create file in a specific directory
  filename = "c:\temp\XL2test.htm"
  If optOutputFile <> "" Then filename = optOutputFile
  Open filename For Output As #1
  Print #1, "<html><head><meta NAME=""robots"" CONTENT=""noi" & "ndex,nofollow"">"
  Close #1
  Shell "notepad " & filename

To create the file in the same path as the open workbook:
  Dim fPath As String
  fPath = ThisWorkbook.Path & Application.PathSeparator
  Open fPath & "Text.txt" For Output As #1

Font List

Utilities   (#utilities)

Search & Replace Utilities that work with HTML (#replace)

HTML -- compare (#compare)

Looking for free HTML comparisons, to be tested.

HTML -- syntax (#syntax)

Web Queries (#webquery)

Web Queries via Excel from the MS KB

Other MS KB articles related to HTML

Wiki (#wiki)

XML and XUL Resources (#xul)


This page was introduced on July 2, 2000. 

 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2009,  F. David McRitchie,  All Rights Reserved