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 Explorer 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 font sizing.  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

  • XL2HTMLa (areas) version which handles multiple area.  will print shaded headings, and center each table. (new 2003-92-09).  An Excel solution that creates pictures of the areas into another sheet can be seen in Ron de Bruin's Printing non-contiguous areas on one page
  • I have created an XL2HTMLX (extended) version which adds the grey shaded Column and Row headers.  IExplore is then invoked for a review and the example (coding) can be copied from view source into a permanent web page.  As the actual purpose is to generate as little code as possible you will have to right justify any cells including numeric cells that you want right justified in your HTML code.  The coding for the XL2HTMLX is based on Hans Herber's simple version.
     
  • Email from Excel and from HTML
  • An archived reply that creates an HTML file using a previously defined user function to format the table row, based on the XL2HTML and XL2HTMLX above.  Very complex formula applied by default to thousands of cells.

  • Repeating Top Row(s) in HTML (when printed) using <TH>...</TH>
     
  • Harald Staff has provided a macro to create a .GIF file by taking a picture on the Excel spreadsheet.
     
  • Tom Ogilvy posted some support MS KB links for XL97 and one for XL5/95 on 1999-10-30 look at the entire thread in Google Usenet Archived postings.
  • For XL97 users the Internet Assistant Wizard Update for Excel is newer than both SR-1 and SR-2 and fixes a potential serious problem; in addition, if you do not install this newer file, the wizard will not work properly with FrontPage 98
  • I use a much less automated method than Charles Balch to transfer my web pages to my website using WS-FTP LE. See details
  • Webbify Excel (oldlink), Dermot Balson -- Update websites from Excel, Download files without web queries, An Excel web browser, An excel web server.
  • HTML Writers Guild, valuable resources from an organization of Web authors
  • HTML-Kit on the Web, Resources, Links and Tutorials available to HTML writers.
  • Spider Web Woman Designs -- Resources, some links to HTML, Front Page, and similar resources.
  • Symbols for HTML and Excel use«, and Unicode symbols.
  • WWW -- Internet starting points, University of Leicester (Uk).
  • InformIT.com : Design & Creative Media > Web Design, will take awhile to go through this to see if it is worthwell, but it was mentioned on LockerGnome [LG 2004-01-29]

    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