Html maker - testers required!

[Richard Schollar]

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!

 

[Sandeep Warrier]

Does the format - Center across selection - also not show up in the HTML?

http://www.mrexcel.com/forum/showthread.php?p=2033525&posted=1#post2033525

I used the format Center across selection for Owners (across cells AD1, AE1 and AF1)

 

[Richard Schollar]

Hi Sandeep

No, Center Across Selection is not recognised by the current code.

 

[snowblizz]

Eager to try this out I get stuck pretty much head on in xl2007.
I select some cells and right click in the menu option and just take the default oen. Gives me a type mismatch error 13.

Function ExcelVersion() As String
Dim temp As String
'On Error Resume Next
Select Case CLng(Application.Version) <-- here

According to the debugger:
Application.Version = "12.0"

Any ideas?

 

[Richard Schollar]

That shouldn't happen - if you go (within the VBE) Tools>References do you see anything marked as MISSING? If so, uncheck these and try re-saving the add-in (you can do this just be closing Excel down as it automatically saves in the code).

 

[snowblizz]

That shouldn't happen - if you go (within the VBE) Tools>References do you see anything marked as MISSING? If so, uncheck these and try re-saving the add-in (you can do this just be closing Excel down as it automatically saves in the code).

I can't see any labelled "missing" they tend to pop up to the top of the list don't they?
I've got 5 references checked: Microsoft Forms 2.0, Microsoft Excel 12.0, Microsoft Office 12.0, OLE Automation, Visual Basic For Applications.

Could system/language settings play in?

 

[Colin Legg]

Yes, it looks like a regional settings issue. So the . in "12.0" is not read as a decimal separator?

I think the workaround would be to use the Val() function instead of CLng().

Give it a try and let us know if it fixes it?

 

[sanrv1f]

A new issue, when I generate HTML with the option - First cell in each column, if there are 10 columns with formulas and in 9 columns the formula starts in row 1 and on the 10th column the formula starts in row 2, the tool generates formula for 19 cells, (ie) 9 cells on row 1 and 10 cells on row2

see here

http://www.mrexcel.com/forum/showthread.php?p=2034887#post2034887

 

[snowblizz]

Yes, it looks like a regional settings issue. So the . in "12.0" is not read as a decimal separator?

I think the workaround would be to use the Val() function instead of CLng().

Give it a try and let us know if it fixes it?

Had the same problem on my home computer and xl2003.
Using Val() instead of CLng() solves this issue. My regional settings uses the "," as decimal and "." as a thousands separator.

 

[Colin Legg]

Had the same problem on my home computer and xl2003.
Using Val() instead of CLng() solves this issue. My regional settings uses the "," as decimal and "." as a thousands separator.

Okay cool. I think using Val() is a good idea also because sometimes the version can have a letter in, so CLng() would also fail there too.

I don't have a copy of the html maker at home so I can't check at the moment, but if it is changed to Val() then you'll also need to make sure that the subsequent Select Case structure remains valid. So something like this:

    Select Case Val(Application.Version)
 
        Case Is >= 13
            MsgBox "Excel 2010 or later"
 
        Case Is >= 12
            MsgBox "Excel 2007"
 
        Case Is >= 11
            MsgBox "Excel 2003"
 
        Case Is >= 10
            MsgBox "Excel 2002"
 
        'etc...
 
    End Select

 

[snowblizz]

That CLng trips me up in another place. If there are cells with text, at least that is what it seems to complain about

Function AnalyzeCell(cell As Range) As String
Dim strFormat As String, varVal As Variant
strFormat = "style="""
With cell
    If .Font.Bold Then strFormat = strFormat & "font-weight: bold;"
    If .HorizontalAlignment = xlRight Then
        strFormat = strFormat & "text-align: right;"
    ElseIf .HorizontalAlignment = xlCenter Then
        strFormat = strFormat & "text-align: center;"
    ElseIf .HorizontalAlignment = 1 Then    'And IsNumeric(.Value)
        On Error Resume Next
        varVal = CLng(.Value) <---here