Html maker - testers required!

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
As some of you may be aware, I have been making an add-in to produce html output of selected data in Excel. The output is fairly consistent with that produced by Excel Jeanie as can be seen here:

http://www.mrexcel.com/forum/showpos...&postcount=241

the differences being:

1. the interface is a whole lot simpler
2. you can choose if you want to output all the formulas in selection or only some of them
3. the ouput colour-codes nesting levels in the formulas
4. it only does formulas and data (it doesn't report conditional formatting or data validation applied)
5. it comes as an .xla file rather than an .exe (so doesn't require Admin rights to install!)
6. the code is visible for anyone that wants to laugh (or cry) at it


The generated html should also be considerably simpler as much of the formatting and style elements are now held in a css file applied to the whole Board (Suat Ozgur has created a css class which I link to in the generated html).

It has been tested by me and Jon von der Heyden and feedback from Suat incorporated. What I need now is for some others to test and come back with any comments.

If you would like to see the file and perhaps test it, please Private Message me with your email address and I'll send you the .xla file and a .doc file with guidance notes:

The .xla file is approx 100Kb in size, so it isn't very big. It will work in all versions of Excel since xl2000, although I will be developing a specific xl2007 ribbon interface next.

Thanks!
 
Last edited:
It hasn't been changed since then Rob ;-)

I can email you the file if you need it again - let me know.
Ok I wasn't sure. I upgraded to Windows 7 at work and everytime I close excel it wants me to overwrite the old file. I haven't had time to look at the code to see what is causing this, but assume it would be something simple.

I am more trying to get everything back to the way I had set up before so I can quickly access things.

Thanks.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Richard, having problems with '<'. Using xl2007, xpPro, htmlMaker20090730a which you sent me in Aug this year (I was trying Html maker because the others (ASAP utils and Excel Jeanie) were showing a similar problem).
See http://www.mrexcel.com/forum/showthread.php?p=2421554&posted=1#post2421554 messages 8 and 9 (and 4). In all cases, when you preview the message, all appears well, but when you actually submit, the result is missing:
< B1:B16
from cell J2's formula. Cell J3 is, surpisingly, OK
I can make it look right if I put spaces around the '<' sign before submitting.
I can send you a file if you want, but it's no more than what you see, just two formulae in two cells.
 
Upvote 0
Rob - that was an interesting problem. I need to look again at how the grid is formed and will consider this too.

p45cal - this should be a straightforward fix achievable via replacing all "<" by < in the output.
 
Upvote 0
p45cal - this should be a straightforward fix achievable via replacing all "<" by < in the output.
Rich (BB code):
I don't know if this is the best way, but this is how I modified the code:
 
Option Explicit
Function FormatFormula(ByVal s As String) As String
Const HTML_COLOR As String = "<font color="""
Static vaColors As Variant
Dim vaOpenBrackets As Variant, vaCloseBrackets As Variant, vaQuotes As Variant
Dim vaFormatClose As Variant, vaFormatOpen As Variant
Dim strPositions As String
Dim i As Long, j As Long, k As Long
Dim temp As String
Dim bSkip As Boolean
 
If IsEmpty(vaColors) Then vaColors = VBA.Array("Blue", "Red", "Green", "Purple", "Teal", "#FF00FF", "Navy")
 
s = WorksheetFunction.Substitute(s, "<", "<")
See here:

http://www.mrexcel.com/forum/showthread.php?t=490700

Hope that helps.
 
Upvote 0
In the original code, isn't that already there with:
Code:
Dim bSkip As Boolean
[COLOR=Red]s = ReplaceChar(s) [/COLOR] 'handle > and < symbols in formulas
If IsEmpty(vaColors)…
and:
Code:
Function [COLOR=Red]ReplaceChar[/COLOR](ByVal s As String) As String
    'deals with tricky characters such as > <
    ReplaceChar = Replace(Replace(s, ">", ">"), "<", "<")
End Function
?

Pascal

Rich (BB code):
I don't know if this is the best way, but this is how I modified the code:
 
Option Explicit
Function FormatFormula(ByVal s As String) As String
Const HTML_COLOR As String = "<font color="""
Static vaColors As Variant
Dim vaOpenBrackets As Variant, vaCloseBrackets As Variant, vaQuotes As Variant
Dim vaFormatClose As Variant, vaFormatOpen As Variant
Dim strPositions As String
Dim i As Long, j As Long, k As Long
Dim temp As String
Dim bSkip As Boolean
 
If IsEmpty(vaColors) Then vaColors = VBA.Array("Blue", "Red", "Green", "Purple", "Teal", "#FF00FF", "Navy")
 
s = WorksheetFunction.Substitute(s, "<", "<")
See here:

http://www.mrexcel.com/forum/showthread.php?t=490700

Hope that helps.
 
Upvote 0
I guess so, but the version I have does not have that, so i must be a little outdated on which version I have. :)
 
Upvote 0
When I paste and do a preview (without changing the original code), all apears OK, but if I do a second preview (or submit) the fault reappears. So perhaps the site, in doing a preview, converts the likes of < and > back to < and > ?
 
Upvote 0
When I paste and do a preview (without changing the original code), all apears OK, but if I do a second preview (or submit) the fault reappears. So perhaps the site, in doing a preview, converts the likes of < and > back to < and > ?
Yes that would be true. If you do a preview or go to edit the post you will have the problem. The board has already rendered it and converted it to a < sign. So then when you look at it, it will be a problem. One fix might be to change this:

ReplaceChar = Replace(Replace(s, ">", ">"), "<", "<")

to:

ReplaceChar = Replace(Replace(s, ">", " > "), "<", " < ")

That may leave the spaces and then when you go to preview or edit, it will haev the extra spaces to not cut off afterwards?
 
Upvote 0
I made the changes to the code, unfortunately, the site seems to strip those extra spaces too on the second preview.:( Grrr.

Yes that would be true. If you do a preview or go to edit the post you will have the problem. The board has already rendered it and converted it to a < sign. So then when you look at it, it will be a problem. One fix might be to change this:

ReplaceChar = Replace(Replace(s, ">", ">"), "<", "<")

to:

ReplaceChar = Replace(Replace(s, ">", " > "), "<", " < ")

That may leave the spaces and then when you go to preview or edit, it will haev the extra spaces to not cut off afterwards?
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,376
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top