This spreadsheet contains the VBA code to write an Excel range as a clean, formatted HTML page, with optional auto-refresh.
NOTE If you click on the ‘This spreadsheet’ link above and it opens a ZIP file or a set of HTML files, right-click on the link and choose Save As.
Features:
- Correctly renders all Excel formatting, including conditional formats.
- Renders embedded charts.
- Generates an HTML page with tabs identical to those in the Excel file.
- Creates clean, formatted HTML that validates to HTML 4.01 strict.
- Optimised stylesheet to ensure minimal page weight.
- Supports merged cells with ROWSPAN and COLSPAN.
- Updates the webpage automatically when cells’ values are changed.
- The webpage automatically refreshes itself at a user-supplied interval.
The result is almost pixel-perfect, compare this Excel screenshot with the HTML page it generated.
14 March 2013. Update, improved lower tab bar to stop over-spilling (thanks to Paul Palmer) and added support for 64-bit Office.
21 August 2014. Many people have asked how to insert an image in the HTML page. It is not possible to get the contents of an embedded Excel image, but it is possible to get an embedded chart. Here’s how to perform this trick:
- In the sheet to be published, make sure no cells are selected
- Excel Menu->Insert->Chart and choose any chart type. This will insert an empty chart.
- Move and size the empty chart as required
- Select the chart by clicking its border
- Excel Menu->Layout and click the Picture icon
- Select an image file (JPG, BMP, etc) and Insert
28 September 2014. Now handles Unicode text correctly.
17 December 2018. Now outputs hyperlinks correctly.
1. It sounds like you added overflow-x to the body and I’m not sure how that will cascade. Perhaps try applying it to the table, in EmitStyles:
WriteLine TAB2 & "table {border-collapse:collapse;table-layout:fixed;overflow-x:hidden;}"
2. The missing anchor for hyperlinks was a bug, which I’ve fixed. Take a fresh copy from HTMLWriter.xlsm
Hello there, thank you so much for this solution. I’d just like to ask two questions if I may:
1. I’d like the tables to be contained to 100% of the div in which they sit, with the overflow-x:hidden (i.e. so that there is no horz scroll)…. I edited the VBA code so that “body {…. ; width:100%; overflow-x:hidden;}” but the results are not consistent (for a workbook with multiple sheets, each outputting an html table, it works for some but not all). Any idea what the cause might be (clearly I have missed something but I am not sure what)?
2. Please confirm that hyperlinks in the workbook would not be supported; I know this to be the case because links in a sheet are output as “plain content” in a as opposed to … so I guess I’m just wanting to know if there is not a way in which they could work?
Thanks again!
In the Setup tab, uncheck “Respect Excel Formatting”. This will remove the widths, but also the colours etc.
If you want to remove solely the widths, you’ll have to remove manually in the source code. Comment this line:
width = "width:" & PointsToPixels(Cell_(row, col).width) - ColLB_(col) - ColRB_(col) & "px"
to
' width = "width:" & PointsToPixels(Cell_(row, col).width) - ColLB_(col) - ColRB_(col) & "px"
Wonderful results with your code.
Is it possible to edit the code so that the HTML cell and table widths are not hard coded? I just need all the widths not to be set or removed.
Thank you.
Hi Maurice,
I’ve now found my chart and your VBA code works beautifully, updating every minute. Thanks very much! (if interested, see map with approximately realtime position of planets at scharrid.blot.im)
Won’t send the excel file because that’s working and it has multiple external references.
Thanks again for the code!
Best regards, Steve
Strange, embedded charts should be output – they do in the conditional sheet in the example.
Can you send me your spreadsheet please?
Many thanks for this, however I can’t seem to get an excel chart to appear in the html.
FWIW, I did notice that there isn’t a checkbox for “embedded objects” on the Setup worksheet, whereas “WH_EMBEDDED” is referred to in the VBA code…. so I tried adding “+ WH_EMBEDDED” to the “WH_DEFAULTS = ” line in the VBA code and cleared all the checkboxes on Setup, but I got the same result. Kindly advise if convenient, thanks!
Hi Max,
no, as it’s written, it can’t generate inline styles.
Maybe put the HTML file somewhere on the web and send a link to it?
Hi, I found your excellent spreadsheet. It does basically what I need with a big exception. I need to include an excel table in an email in html. For this kind of purpose, an inline style is the best option.
Have you thought to include a new option/setting for this?
Thanks a lot, regards,
M
There is no concept of ‘freeze panes’ in html, sorry
Hi Maurice
This is excellent, congrats on creating this.
We use the save as html function embedded in excel and republish everytime the doc is saved, however the freeze panes on the pivots are not there in HTML.
This also seems to be the case with this tool, or am I doing something incorrectly? Is there a way of keeping the frozen / locked panes in the HTML output.
Many Thanks
The only way I know to do this is with SharePoint
Hello Sir,
Greetings.
I need to build a website and incorporate certain finance calculators in it, which are built in excel and I can not import them directly or write formulas for them using java script because they are dependent on large number of factors and thus I need to embed excel worksheets directly into it that can reflect dynamic changes.
So is there any possibility to embed excel workbooks to an html webpage or say a website where dynamic changes can be performed.
Excellent. Thanks!
Try clicking the ‘Publish’ button.
Hello Maurice, I am trying to use the spreadsheet and I do everything as in the readme tab, but the html is not generated. What could be wrong?
Thanks, Ivonne
In the Setup tab, un-check “Write complete, tabbed workbook”
A common problem. Explained in the NOTE here
Hi Maurice,
I’d really like to see the code that you used, but I can’t seem to download the .xlsm file I keep getting the .zip files. I tried right clicking on the link but I can seem to see the options for save as. could you help me?
cheers,
Ian
Using Excel 2013. The named range is not used in the file name but the Sheet name is used. It also saves all the named ranges every time. Is there a way to control what named ranges are saved.?
Filenames:
WeeklyValues.ws.Conditional.html
WeeklyValues.ws.Demo.html
WeeklyValues.ws.README.html
WeeklyValues.ws.Week.html
WeeklyValues.html
No, rotated text isn’t supported, sorry.
Yes, the trick is to put the image in a chart. I’ve added the instructions to the first post at the top of this page.
This is a great bit of code.
1. Does it support text alignment? Vertical text? I tried it out and all the vertical text changed to horizontal.
2. Does it support any images? I published a spreadsheet with an image and it did not include it.
thanks!
This cannot be done in Excel, Excel cell formatting is not HTML
Hi Maurice, We are trying to convert the html content from a cell to Formatted data using Clipboard function and pasting formatted content to new cell. the issue is, it is not fitting in that cell, it is spreading across the cells. is it possible to paste to the particular cell.
Below content we are trying to format and paste to a particular cell
This is BOLD This is Italicized This is REDThis is BOLD Italicized This is nderlinedThis RED BOLDBullet 1Bullet 2
Right-click on the link and choose Save As
How can i download HTMLWriter.xlsm file? When i click on the link provided, its asking me to save a zip file which contains HTML pages instead of excel file.
Hi Maurice, This is a gem of a code.
There is one bug that it does not seem to convert databars into HTML. The template you provided results in no databars in the HTML file. When I try my own sheets, I also get an error in the databar section.
See “Case xlDatabar ” section in Sub Conditional (row As Long, col As Long, cell As Variant)
Thanks!
Nishant
I’d suggest you try rather than xlScreen
Below is the code I have been using to post a range for a sheet. It posts as a image with terrible resolution. Your sample results look amazing. But I am having trouble converting your code to grab my range. I should admit I am a hack on writing code, just figuring out as I go.
Lastly I found this thread while trying to learn how to make my webpage be an auto refresh webpage. Apparently I need to get this at the beginning of my title section of the HTML file.
Thanks for any help to get me going.
FC_Name = Worksheets(“Settings”).Range(“C14”)
Sheets(“Flow Dashboard”).Select
Range(“A1:AW40”).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets(“TV”).Select
Range(“A1”).Select
ActiveSheet.Paste
Sheets(“Flow Dashboard”).Select
With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
“\\ant\dept-na\PHX5\General\Flow Balancer\” & FC_Name & “_Flow.htm”, “TV”, “$A$1:$AW$53”, xlHtmlStatic, _
“Flow History”, “”)
.Publish (True)
.AutoRepublish = False
End With
End Sub
It would be relatively easy, yes, but I’m afraid I haven’t the time to perform custom programming.
Is it possible to add this table search function to the vba script ??
example:
Jquery Search Text In HTML Table
$(document).ready(function() {
$(‘#search’).keyup(function() {
searchTable($(this).val());
});
});
function searchTable(inputVal) {
var table = $(‘#searchTable’);
table.find(‘tr’).each(function(index, row) {
var allCells = $(row).find(‘td’);
if (allCells.length > 0) {
var found = false;
allCells.each(function(index, td) {
var regExp = new RegExp(inputVal, ‘i’);
if (regExp.test($(td).text())) {
found = true;
return false;
}
});
if (found == true)
$(row).show();
else
$(row).hide();
}
});
}
Search Box
#
Name
Company
Designation
1
Sandeep
Infosys
Developer
2
Pramod
TCS
Manager
3
Narendra
Accenture
Technical Lead
4
Tarun
Metacube
Business Analyst
5
Manish
Wipro
QA Engineeer
6
Ravi
Satyam
Sales Manager
7
Bhawani
HCL
HR Manager
8
Yashpal
Nestle
Vice President
9
Akhilesh
Hero Honda
Delivery Manager
10
Anand
Tech Mahindra
IT Manager
I send the file Maurice, did you receive it?
Thanks for your help!
Looks like there’s a bug in the conditional formatting.
Can you send me a copy of the “Ground handling status.xlsm”, I’d like to see how the JPEG is embedded and the conditional formatting options?
1. Can you send me an Excel file with unicode characters so that I can see why they arn’t rendering?
2. I suspect that in-cell formatting would require a lot of work, but I’ll have a look
Can you send me an Excel file that has umlauts, so that I can see why they aren’t rendering? maurice AT calvert DOT ch
Don’t know if the screenshot came through in my previous post but this should be it: http://www.mediafire.com/view/7x4aqydviqi5s4i/HTML_Writer_Problem.jpg
Thanks!
I’m using you HTML Writer Code to publish a webpage in a project I am working on.
And for the most it’s working perfect, I changed the code a bit so it will only publish one page in my sheet.
I do see 2 things that I’d like to fix, but can’t find how.
– I did a conditional formatting on the Fueled and Loaded columns, 3 conditions, green when value is 1, red when value is 0, and white when value is empty. This works fine in Excel but when I use your code the white cells are displayed as if they have the 0 value. How can it be fixed.
– And the jpg image in the top left of the page isn’t showing on the webpage. When I did a manual save as html it did show the image. Any idea why? And how it can be enabled?
Screenshot:
Thanks in advance for your help.
Kind regards
Respect man!
I support Hans request, tooltips would be great.
2 other enhancemens:
– support the Unicode characters, like ↗ (ChrW(8599))
– support in-cell formatting (part of the text in bold…)
Wow Maurice, your macro is a blessing! – I would also pay for this!
Just one or two things which are not working perfectly to me.
1) When I open the HTM or HTML file, umlauts (Umlaute such as ä,ö,ü..) are displayed as a square.
2) I was downloading your script, because I was looking for a macro which allows me to output a html file which supports “freezing” or “locking” columns or rows. Have not seen this in your macro – is this doable? – Could please you give me just a hit how to implement that?
Thanks a lot,
Stefan
Looks really nice, thank you very much.
Now if cell comments would be exported as tooltips it would be perfect
OMG. You just saved me God knows how many hours of work.
Straange that.
Try renaming HTMLWriter.zip as HTMLWriter.xls
The formatting should work fine. Can you send me an example? maurice AT calvert DOT ch
Hi Maurice,
I’m also having problems seeing the Excel workbook. I definitely have Excel 2007 installed, I used the ‘save as’ option, and the file HTMLWriter.zip downloads.
After saving, how do I access the workbook?
Kind regards.
It is excellent. But it doesn’t work for formatting cells.
If formatting is included in the excel file, in html it looks raw data and unable to format or sort.
If u could add it will be really great!
It shows up as a ZIP file because you don’t have Excel 2007 (or later) on your PC.
Try right-clicking on the link and ‘Save as…’
The download link downloads a zip folder, how do I access the excel file?
There are detailed instructions in the Excel workbook.
What help do you require?
Can you please help me how to use this zip files to convert excel to HTML files.
No, Excel 2007 or later
Try clicking on the first two words “This spreadsheet” >;-)
How does this work?
Also, where is the updated(latest) code?
Thanks.
Hi Maurice,
Many Thanks n great job bro, are this speadsheet can work in excel 2003?
I try this in excel 2003 is’t work. Please Help!
Warm Regards,
Hi Maurice – wow, this is wonderful! Can this also work with controls, like checkboxes?
Thank you for the compliment 🙂
Your tool is great. In my opinion, you should sell it! I have 20 years of software development and I am impressed!
This thing rulez. Wish it would be coded as a Powershell script but this should also work perferctly.
The code outputs the contents of the Excel cell, without formatting (so that viewers of the web page can use a webquery to pick up the data in a machine-readable format).
Works great. Thanks.
Some of my cells have Currency Format and the £ sysmbol does not get converted correctly. Any ideas?
This code was a lifesaver for me today. Thanks!!!
I have uploaded the latest version, which new supports conditional formatting, embedded charts and tabbed worksheets.
This is very nice. One question though, my select range includes graphs but its not outputted to the html file. Is there any way to include graphs
Excellent! There is only a small problem with converting colors outside of main palette (defined as RGB for example 227,227,227 – you give FFCC99 instead of E3E3E3) but it is really nothing. Thanks for sharing 🙂
Thanks for the feedback, I’ve fixed the link
This looks really interesting — thanks.
Note that the download link looks a bit broken — it’s relative rather than absolute (so it’s pointing to https://www.calvert.ch/maurice/2012/01/13/convert-excel-to-html-with-formatting/www.calvert.ch/download/HTMLWriter.xlsm rather than http://www.calvert.ch/download/HTMLWriter.xlsm)