This two part article consists of:
Part A – Converting HTML to Excel using Javascript
Part B – Exporting Excel to HTML using VBA
HTML to Excel using javascript
Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document into a Excel workbook we need to carry out three steps – (a) Parse the table into its constituent rows, columns and cells (b) Use ACTIVEX to initialize a new Excel workbook and finally (c) Feed the workbook with data from step (a).
During step 1, we try to get a handle on the table in the html document using the getElementsByTagName(“table”) function. Once we have a handle on the table, we try to ascertain the number of rows (<tr>) and the number of columns that exist within that table (<tr> within a <td>). Once we know both of these, conversion form html to excel is carried out by reading each cell’s content using the javascript for loop and pushing them into the Excel worksheet (created using ACTIVEX).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <html> <head> <script type="text/javascript"> function write_to_excel() { str=""; var mytable = document.getElementsByTagName("table")[0]; var row_Count = mytable.rows.length; var col_Count = mytable.getElementsByTagName("tr")[0].getElementsByTagName("td").length; var ExcelApp = new ActiveXObject("Excel.Application"); var ExcelSheet = new ActiveXObject("Excel.Sheet"); ExcelSheet.Application.Visible = true; for(var i=0; i < row_count ; i++) { for(var j=0; j < col_Count; j++) { str= mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML; ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str; } } } </script></script></head> <body> <input type="submit" value="Export to EXCEL" onclick="write_to_excel();"/> <!-- ************************************************--> <!--**** INSERT THE TABLE YOU WANT EXPORT HERE ****--> <table><tr><td>First</td><td>second</td></tr></table> <!-- *******************example given above****************--> </body> </html> |
(note: remove the extra <script> tag from the above code just before the head tag).
As you can see, we first parse the table, then invoke ACTIVEX to create an excel object and then export our table to it.
You can download a webpage with code to export HTML to Excel here or click on the button below:
Excel to HTML using VBA
Excel can convert either a specified range or an entire workbook (including all its sheets) into an html document. The first part (converting a specified range to an html document) can be achieved by using the PublishObjects property of the workbook. Let’s look at a sample code:
1 2 3 4 5 6 7 8 9 10 11 12 13 | Sub EXCEL_TO_HTML_RANGE() Dim path As String Dim rng As Range path = Application.ActiveWorkbook.path & "\Book1.htm" Set rng = Range(Cells(1, 1), Cells(10, 3)) With ActiveWorkbook.PublishObjects.Add(xlSourceRange, path, "Sheet1", _ rng.Address, xlHtmlStatic, "Name_Of_DIV", "Title_of_Page") .Publish (True) .AutoRepublish = False End With End Sub |
If you open the resulting html file, you will see that the specified range has been converted into a table and has been properly enclosed with the opening and closing <table> tags. If you go back a bit, you’d remember that we talked about the same thing when we were converting html to excel. This is the key, the common denominator – that allows the data conversion to take place both ways from html to excel and back from excel to html. Also note that the PublishObjects function has the following syntax:
PublishObjects.Add(XlSourceType, FileName, Sheet, Source, HtmlType, DivID, Title)
where:
XlSourceType is the type of object that needs to be exported to HTML. The following can be specified as the source types:
FileName is the path alongwith the name of the output html file
Source is the object which needs to be converted to html. It can be a range, a chart or any of the other source types as specified in the XlSourceType parameter
HtmlType is the type of conversion method you would like to employ. The following can be specified as the html types:
DIVID is the ID of the div tag that will be used to enclose the table in the html document generated by the code.
Title is the title of the html document generated by the code.
The other part is to convert the entire workbook into an html document. This is slightly different as it uses the in-built converters in excel to achieve this goal. The code shown below can be used to convert an Excel workbook to any of the other available formats – all that needs to be done is to specify the proper file format (FileFormat property) and the extension.
1 2 3 4 5 | Sub EXCEL_TO_HTML_WORKBOOK() Dim path As String path = Application.ActiveWorkbook.path & "\Book1.htm" ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlHtml End Sub |
where
FileFormat is the type of output format to produce. We use the xlHtml format (.html) but the same can be used to produce other formats:
You can download a sample worksheet with VBA code for converting excel to HTML here or click on the button below:
Excel Chart Maximum Y Axis Value and Axis Interval (Major Unit) 
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Multithreaded VBA – An Approach To Processing Using VBScript 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
Treemap Add-In For Excel – Treemap Software Demo 
Treemap in Excel – Coming Soon 
the code was very useful.
August 30th, 2010 at 6:01 amrafi.mohamad@gmail.com
SH95079060
code is not working for html page into excel sheet conversion
can u pls send the right code in my email id
December 26th, 2010 at 2:37 pmcan u pls help me 4 my project.
December 26th, 2010 at 2:42 pmthe code which given above is not working
even i downloaded ur file still that file not converting html into the excel sheet
pls help
not working
June 8th, 2011 at 9:40 pmthis indeed is a brilliant code.
September 1st, 2011 at 10:05 ambut what would one do if the table is generated after submitting a html form and verified by a javascript?
It does not work with .cshtm
October 10th, 2011 at 9:50 pmThis solution probably only works for IE:
Tools, Internet Options, Secuirty, Custom level
Enable “Initialize and script ActiveX control notmarked as safe.”
February 15th, 2012 at 12:57 pmIn “HTML to Excel using javascript”, in the 1st for loop, the row_count should be row_Count.
June 6th, 2012 at 5:30 amThe code for “HTML to Excel using javascript” only pushes to one column in excel. How do you allow excel to read any of the other columns in this script?
June 6th, 2013 at 3:28 pmI would recommend you to use Aspose.Cells for Java for converting excel files to html because its a secure API and uses cloud technology and offers many features and also provides developers with alot of sample codes which they can use in their API.
July 5th, 2013 at 2:45 amI tried using export to excel, i.e. HTML to excel code but it is not working.
March 16th, 2017 at 12:56 am