Excel to HTML conversions

XL2HTML and XL2HTMLX macros

The generated code will be about one third the size of the humonguous 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 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.  You definitely will not see the results of Conditional Formatting because that is difficult to pick up.

The Excel save as, save as webpage and HTML wizards on the otherhand 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.

XL2HTML, converts selection on an Excel sheet to HTML

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

A brief look into what HTML is, limitations, etc.

• In HTML multiple spaces are the equivalent of a single space.  All multiple spaces will be reduced to a single space.  To guarantee an extra single space one must code   (non-breaking space).
• HTML is very good a determining it's own widths required for columns and the width of a table. 
• HTML does not print more than one page wide, though you can view more by scrolling on the screen.

What these macros do

    'Original coding and concept is based on
    'http://www.herber.de/mailing/020598v.txt
    ' Hans W. Herber * Microsoft Excel MVP
    'Major changes D.McRitchie, 1998-08- msgbox, close,
    ' http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt
    ' Shading option, Column & ROW headings and will use right 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 <BR> for Chr(10)
    'rev. 2000-07-03 for Center Across Selection (cell format)

Preparation of an Excel file for conversion using XL2HTML or XL2HTMLX

• Icons and shapes will not be reproduced.  Remember this is only a tool to create a small simple TABLE without all the space wasting bells and whistles.
• Hyperlinks will not be generated for =HYPERLINK(...,...) nor from such things as www.abc.com presented instead of http://www.abc.com in order to keep generated HTML small.  Also consider the difficulties of parsing HYPERLINK worksheet statements and determining what would be wanted in HTML for such statemenrs as:
    =HYPERLINK("""" & A1 & "!" & B1 & """,""" & A1 & "!" & B1 & """")
    =IF(D1=0,"David McRitchie",HYPERLINK("DMcRitchie@msn.com","DavidMcRitchie"))
• The program does not change pointsize but will recognize color, bold, and italic, so you might change some descriptive headings accordingly. See example:    Shortcut Keys in Excel 2000 ,  which was created to extend but not replace information already found in Shortcut Keys in Excel 95.
• Since XL2HTML/XL2HTMLX will handle merged cells, it would be advantageous to merge cells across columns so as not to interfere with the adjustment of column widths.  If you merge a cell across 3 columns those three columns will occupy at least as much space as is required by any cell in the column or any merged cell in the columns of merged cells.
• Merged columns can be hard to work with in Excel though, so this may be easier to work with both the Excel and conversion to HTML.  Use Center Across Selection in the format, cells, alignment and make sure Merged cells is not on.  This will be recognized by XL2HTMLx. 
• Use of Alt+Enter as a line break, CHR(10), to wrap cell is now recognized by XL2HTMLx and will produce an HTML <BR> tag which will cause lines to break within a cell at that point.  In HTML all cells are wrapped, but only when necessary to fit width of display, where a line breaks would otherwise be determined by HTML. 

   
  

  Experiment by widening and narrowing window

  	A	B	C	D	E	F	G	H	I	J	K
  1	Sample without merging cells				Sample merging cells				Sample merging cells
  2	3006	6d nails	0.99		3006	6d nails	0.99		3006	6d nails	0.99
  3	3008	8d nails	0.99		3008	8d nails	0.99		3008	8d nails	0.99

  Center Across Selection (Format,cells,alignment)

  	A	B	C	D	E	F	G	H	I	J	K
  6	Center Across Selection				Center Across Selection		x		Center Across Selection	x
  7	3006	6d nails	0.99		3006	6d nails	0.99		3006	6d nails	0.99
  8	3008	8d nails	0.99		3008	8d nails	0.99		3008	8d nails	0.99

  in this example B6:C6,F6,J6 are empty; there is an "x" in G6 and K6
  Note:  HTLM representation will not match Excel when HTML wraps or the content exceeds the width of the affected cells in Excel.  In Excel the content may overlap adjacent empty cells but remain centered on the involved center across cells.

Simple .BAT file to merge files

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

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"
  Shell """c:\program Files\Internet Explorer\iexplore.exe"" c:\temp\billsweb\composite.html"
End Sub

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.

Internet Explorer, Netscape and Excel

Differences in versions of Excel

Pasting IE Favorites into Excel 2000

• In IE use File, Export to a temporary file of your choice
    i.e.  c:\temp\iebookmarks.htm
• Open your newly created HTML file with IEsince only IE will allow you to paste into Excel nicely.
• Edit, Select ALL, and copy (ctrl+A, Ctrl+c) and Paste into a new sheet in your workbook (Ctrl+v).  Indentations are created by TAB. 
• In column B, which should be empty, copy down the following formula
      =URL(A1) or =personal.xls!url(a1)
  The URL macro is described within the BuildTOC documentation.

Pasting Netscape Bookmarks into Excel 2000

Sorting Bookmarks or Favorites, and Sorted Menus

Bookmarks (Netscape), and Favorites (Internet Explorer)

The information in this topic has nothing to do with Excel, but relates to sorting and printing of bookmarks.

Printing Bookmarks.  Any of your Netscape bookmark files can be printed as they are HTML files (i.e. Bookmark.htm).  Your single IE Favorites must be exported to an HTML file first using Export from the file menu of Internet Explorer -- be very careful to rename the file so you do not wipe out your real Netscape bookmarks.

Display of the URL in a Hyperlink such as from a column of bookmarks can be done with a User Defined Function such as URL function on my Build Table of Contents page which has lots of information on hyperlinks.

Bookmarks:  Netscape 3 versions provide for sorting bookmarks.  Later versions of Netscape (4 and up) and Internet Explorer do not allow permanent sorting of bookmarks alphabetically.  In order to sort files properly (folders first) a space in inserted in front of each heading (directory) and then they can be sorted with Netscape 3.7.

Ever since IE came out neither Netscape nor IE can sort the bookmarks nicely.  But if you have an old Netscape version (version 3) you can sort the bookmarks very nicely after a little preparation.  If working from IE export the favorites as described earlier.  Edit a copy of Netscape bookmark file (in case you mess things up).  Each folder (heading item) should begin with a single space so they sort ahead of individual bookmarks in the topic.  You can use a Tilde after the space for folders you want at the very top.  Using an old «Netscape 3.7« version you can sort the bookmarks.  Check out the results, and rename your old bookmarks and new bookmarks.  If they were IE5 you can delete your old favorites and Import the new bookmark file into your favorites.  Working with over 800 bookmarks is not fun in the current versions of Netscape and IE5.  update: IE6 on Win98 still did not sort favorites.  IE6 on Win2000 automatically sorts favorites and does it properly with the folders first; nevertheless, you cannot export them as a sorted bookmark file.

The bookmarks file uses dictionary list (<dl>,<dt>,><dd>) the bookmark wizard mentioned below inserts heading levels and does not have bookmark things like last time used indicators.

IE did something to mess this up quite a bit.  In the imported bookmarks some of the folders are repeated later and not sorted or mixed in with the riff-raff uncategorized bookmarks.  Solution: delete all of the IE bookmarks then reimport the sorted favorite bookmarks.  2001-04-25.  When you have 616 directories, subdirectories, and urls working with favorites is not pleasant at all, actually it is next to impossible.  In Netscape I had 1723 directories, subdirectories, and urls and had no problem managing them and sorting them with Netscape 3.0. 

Freeware version of Download CustomMenu V4.30 Freeware Release on 10/28/2000, which provided a more friendly interface to bookmark type entries.  Not bad, if using bookmarks as bookmarks is your only use of bookmarks. http://www.yellowriversw.com/download.htm.  Available versions to process IE Favorites, and Netscape Bookmarks.

Bookmark Wizard « can be downloaded from Moon Software http://www.moonsoftware.com/ which creates a web page from your IE Favorites file.  The list is sorted with individual files above the sub-directory folders.  The opposite of the way I sort them, but they are organized unlike the Favorites. 

Current state of bookmark files:  The only decent treatment of bookmarks is from the Old Netscape that sorted bookmarks permanently, were regular HTML files that you could include descriptions of the articles, and have any number of additional (separate) bookmark files.  All of the other software to organize bookmarks and substitute for bookmarks is sheer nonsense, has large overhead by comparison, and you have no idea whether they are extracting marketing information from your usage.  The nonsensical IE Favorites list is strictly a marketing tool to place their own bookmarks in with your own, is difficult to manage, cannot be read as web page with bookmarks and descriptions, and Netscape has bought into this nonsense by no longer providing the means of permanently sorting bookmarks and also are tampering with your bookmarks by injecting their advertising bookmarks just like in IE.

Acknowledgments

Related

Excel to HTML Specifications and Syntax checking (most have downloads available)

• HTML 4.01 Specification«, the page I reference the most is the elements«. 
  Actually I keep a copy on my HD see top of the specification for obtaining a zip file copy.  W3C Search
• HTML Interesting Information strictly HTML (no Excel information at this link). 
• HTML-Kit provides a free editor and includes David Raggett's "Tidy HTML", which together provide a power editor, and syntax checker.«  HTML-Kit has it's own newsgroups  [a href="news://chamisplace.com/chami.public.htmlkit.misc">news://chamisplace.com/chami.public.htmlkit.misc]
• CAST Org Bobby, Welcome to Bobby 3.2, syntax check your HTML for use by persons with disability, single URL, or download for the ability to check many files.  Site mentioned in Tidy HTML messages.  (What is CAST)
• W3C® Link Checker

HTML information on Microsoft sites

• Converting Excel 97 Data to HTML«, The HTMLconvert function is provided by the Internet Assistant Wizard Add-In.  The page describes using the Internet Assistant.  You may need to obtain the Updated Assistant before safely using this.  Generates excessive font information but not the XML junk that XL 2000 does.  http://www.microsoft.com/ExcelDev/Articles/ConvHTML.htm
• Microsoft Excel 97 on the Web -- Get the information you need to take advantage of the Microsoft Excel 97 Web integration features and functionality.
• Microsoft Office Resource Center.  Of particular interest is the Excel to Web Table Converter (XL5 and up) which will convert selected cells to HTML and can include borders for empty cells.  Includes source code.  XL97 users especially will appreciate this one since MS dropped option choices in its converter (Save As).  You might want to compare it to MS KB q168561  XL97: How to Programmatically Save a Worksheet as HTML.  Problem: q172305 XL97: LastUpdated Argument of Htmlconvert Is Not Functional
• Microsoft® Office HTML and XML Reference and element declarations describe the files saved by Microsoft® Excel, Microsoft® PowerPoint®, and Microsoft® Word when a document, presentation, workbook, or worksheet is saved as a Web page.
• Microsoft Office XP Resource Kit: HTML Help Workshop, Creating HTML HELP files.
• Office 2000 HTML Filter 2.0 « [http://office.microsoft.com/downloads/2000/Msohtmf2.aspx] -- I have tried this, it reduced the size of the HTML to 1/2 but it is still garbage.  It still has all the garbage style and font information that I don't want.

HTML information on non MS sites

• Charlie's VBA to Excel to HTML code HomePage Converts an Excel range to an HTML Table, Charles Balch, most formatting is preserved.  Font size, row & column height are ignored.  Creates a smaller file than the Internet Assistant in XL95/XL97 and Save As in XL2000 (Tools --> ...).  Designed to convert an Excel spreadsheet and automatically place it on the server using FTP.  Of particular interest to teachers in putting grades out to the internet see examples of the macros output in any link to grades in Charlie's CIS Home Page [href="http://cis.balch.org/cis/cis.html] -- note no passwords are used but the identity of each student is hidden in the coded name field, also of interest are his World Wide Web/HTML Information and Reading Room links at the bottom of his CIS Home page.  Code also works well in Excel 2000. 
• How to create formatted messages in Microsoft Outlook Three methods to send formatted messages in Outlook.  Remember that Outlook 97 cannot handle HTML format messages, and that rich-text (RTF) works only with Outlook and the Exchange client and requires a setting not just on the message format, but also a setting for each recipients.  Message-ID: <3c7475ac_1@newsa.ev1.net>
• XL2HTML-Konverter for XL5/7 hwkkonv.zip by Hans W. Herber (site is in German) -- I don't understand German and ended up with an addin that turned calculation off even though the addin was not being used.  I highly recommend his simple conversion to HTML without fonts, shading, alignment etc. coded in English (under 30 lines) as an example of how simple the coding actually can be and to generate only minimum code.  I use the simple version for the simple things and the MS Excel Internet Assistant addin (XL95) when I need to be fancy.  For those of us who speak only English you might open up a window in German and a Go Translator German to English version.
   
• I have created an XL2HTMLX (extended) version which adds the grey shaded Column and Row headers.  IExplorer 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.
   
• 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, 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.

Stripping out the Garbage from MS conversions to HTML

• The Office HTML Filter will remove all Office 2000 extra formatting tags from an HTML file. XL2000 HTML filter -- http://officeupdate.microsoft.com/2000/downloadDetails/htmlfilter.htm?s=/downloadCatalog/dldExcel.htm -- http://officeupdate.microsoft.com/2000/articles/htmlfilter.htm --- http://office.microsoft.com/Downloads/
• strip your HTML of the embedded XML data by using the MSOHTML "cleaning" tool from http://officeupdate.microsoft.com/isapi/goouprem.asp?TARGET=/download/officeupdate/2000/msohtmlf.exe
• Save A Range To Clean HTML: A discussion of some of the options surrounding the conversion of worksheet ranges to HTML tables with my optimal solution to producing lean download-friendly web output from Excel. -- Rob Bruce (I haven't tried this -- david)

File Input/Output

• File Access with Visual Basic® for Applications, describes how to process files with the file input/output (I/O) functions that Visual Basic for Applications provides -- information on using Sequentiai, Random access, and Binary.  The examples show are for a .CSV type of file for Firstname, Lastname, etc.

Font List

• A listing of installed fonts on your system via HTML with http://www.bitstorm.org/fontlist/ or in Excel

Web Queries

• web query Stratos Malasiotis, 2000-07-10. Another reply by Stratos not involving HTML but replacing a line in a flat file.

Web Queries via Excel from the MS KB

• Microsoft Excel 97 on the Web web queries is at end of article.
• Q157482 -- :XL97: How to Create Web Query (.iqy) Files
• Q162080 -- XL97: How to Programmatically Perform a Web Query

Other MS KB articles related to HTML

• Q212366 -- XL: Rotated Text Displays Horizontal When Viewed In Browser.  Rotated text not supported in HTML.
• Q277621 -- XL2002: Export to Microsoft Excel Command in Internet Explorer Does Nothing. Excel was reinstalled to a different location (fix registry) or Excel was removed from system.
• Q282161 -- XL2002: Opening XML Files in Excel.
  In XML you can define an unlimited set of tags. Tags are used to describe the type of data that is enclosed.

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.