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!

 

[Colin Legg]

Actually it was a bit more complicated when combined with the merged cells point because merged cells seem to affect the currentarray property...

I'll e-mail what I've done to you...

 

[Richard Schollar]

No sane person should ever use merged cells. Since you are now ex-beard and thus stand a chance of qualifying as compos mentis, you should not entertain notions of merged cells.

 

[Richard Schollar]

Note on latest realease (Htmlmaker20090716.xla)

Kris has reported a rather serious bug in the release which occurs if the workbook doestn't contain names of any kind and if the output range does contain formulas. The code then bugs out. This is easily correctible - I will release a corrected one when i get home tonight or if you want to modify the code in the current release then you need to replace the Make_Grid() sub in the mCreateHTML module with the following code:

Sub Make_Grid(Optional ByVal generator_setting As Variant)  'must be set as variant to use IsMissing
 
    Dim rngWhole As Range, vGrid As Variant, vWBnms As Variant, vWSnms As Variant
    Dim strOutput As String
    Dim objData As DataObject
    Dim rngFormulas As Range, rngTemp As Range, ar As Range
    Dim lngRows As Long
    Dim blnHasNames As Boolean
    Dim i As Long
    Dim FormulasToParse As FormulaSettings
 
    blnHasNames = (ActiveWorkbook.Names.Count > 0)
 
 
    If IsMissing(generator_setting) Then
        FormulasToParse = ThisWorkbook.Worksheets("Settings").Range("A1").Value
    Else
        FormulasToParse = generator_setting
    End If
 
    Clear_Clipboard
 
    On Error Resume Next
    If Selection.Count = 1 Then
        Set rngWhole = Selection
    Else
        Set rngWhole = Selection.SpecialCells(xlCellTypeVisible)
    End If
    On Error GoTo 0
    If rngWhole Is Nothing Then Exit Sub
 
    'not efficient for every case but will work!:
    Generate_Grid vGrid, rngWhole
 
    If IsEmpty(vGrid) Then Exit Sub  'check to see if max col or max rows exceeded
 
    Set objData = New DataObject
 
    strOutput = "<b>" & rngWhole.Parent.Name & "</b><table class=""html-maker-worksheet"" border=""1"" cellspacing=""0"" cellpadding=""0"">" '
    strOutput = strOutput & Grid_2_html(vGrid)
    strOutput = strOutput & "</tbody></table><b>" & ExcelVersion() & "</b><br /><br />"
 
    On Error Resume Next 'turn off error reporting so next bit doesnt fail
    If rngWhole.Count = 1 Then
        If rngWhole.HasFormula Then Set rngFormulas = rngWhole
    Else
        Select Case FormulasToParse
            Case AllFormulas
                Set rngFormulas = rngWhole.SpecialCells(xlCellTypeFormulas)
            Case FirstCell
                Set rngFormulas = rngWhole.SpecialCells(xlCellTypeFormulas).Cells(1, 1)
            Case FirstCellInColumn
                Set rngTemp = rngWhole.SpecialCells(xlCellTypeFormulas)
                If Not rngTemp Is Nothing Then
                    For Each ar In rngTemp.Areas
                        For i = 1 To ar.Columns.Count
                            If rngFormulas Is Nothing Then
                                Set rngFormulas = ar(1, i)
                            Else
                                Set rngFormulas = Union(rngFormulas, ar(1, i))
                            End If
                        Next i
                    Next ar
                End If
            Case NoFormulas
                Set rngFormulas = Nothing
            Case UserDefined
                Set rngFormulas = Application.InputBox("Select cells to include in output", "Select Formula Cells", Type:=8).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeFormulas)
            Case Else
                Set rngFormulas = rngWhole.SpecialCells(xlCellTypeFormulas)
        End Select
    End If
    On Error GoTo 0
 
    Dim cell As Range, rngHasArray As Range, rngNormalFormula As Range
 
    If Not rngFormulas Is Nothing Then
 
        'extract defined names in activeworkbook referenced in formula:
        If blnHasNames Then Identify_Used_Names rngFormulas, vWBnms, vWSnms
 
        For Each cell In rngFormulas
 
            'if the cell is merged we only want the first cell in the merged area
            If cell.MergeArea.Cells(1).Address(False, False) = cell.Address(False, False) Then
 
                'does the cell have an array formula?
                If cell.HasArray Then
 
                    'if it is a single cell array formula or a merged cell
                    If (cell.Address(False, False) = cell.CurrentArray.Address(False, False)) Or (cell.MergeArea.Address(False, False) <> cell.Address(False, False)) Then
 
                        If rngHasArray Is Nothing Then
                            Set rngHasArray = cell
                        Else
                            Set rngHasArray = Union(rngHasArray, cell)
                        End If
 
                    'if it is a multiple cell array formula
                    Else
 
                        If rngHasArray Is Nothing Then
                            Set rngHasArray = cell
                        Else
                            If Intersect(rngHasArray, cell.CurrentArray) Is Nothing Then
                                Set rngHasArray = Union(rngHasArray, cell)
                            End If
                        End If
 
                    End If
 
                'it is not an array formula
                Else
                    If rngNormalFormula Is Nothing Then
                        Set rngNormalFormula = cell
                    Else
                        Set rngNormalFormula = Union(rngNormalFormula, cell)
                    End If
                End If
 
            End If
        Next cell
 
        If Not rngNormalFormula Is Nothing Then _
            strOutput = strOutput & "<table  ><tr><td style=""padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;""><b>Worksheet Formulas</b>" _
                & MakeFormulaTable(rngNormalFormula) & "</td></tr></table><br />"
 
        If Not rngHasArray Is Nothing Then _
            strOutput = strOutput & "<table><tr><td style=""padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;""><b>Array Formulas</b>" _
                & MakeFormulaTable(rngHasArray) & "<b>Entered with Ctrl+Shift+Enter.</b>If entered correctly, Excel will " & _
                "surround with curly braces {}. <b>Note: Do not try and enter these manually yourself</b></td></tr>" & _
                "</table><br />"
 
        If Not IsEmpty(vWBnms) Then _
            strOutput = strOutput & "<table><tr><td style=""padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;""><b>Workbook Defined Names</b>" _
            & MakeNameTable(vWBnms) & "</td></tr></table><br />"
 
         If Not IsEmpty(vWSnms) Then _
            strOutput = strOutput & "<table><tr><td style=""padding:0.3em;border: 2px solid #000009;background-color:#FFFFFF;""><b>Worksheet Defined Names</b>" _
            & MakeNameTable(vWSnms) & "</td></tr></table>"
 
    End If
 
    objData.SetText strOutput
    objData.PutInClipboard
End Sub

Changes are in red.

 

[Richard Schollar]

Another change:

I have modified the formula output so that the additional spaces after the closing brackets are not required (should really have thought to look for an applicable css style to do this previously - still, better late than never).

This will be in tonight's release.

 

[Richard Schollar]

A question for everyone: should there be a warning message box or some such notifying that the copy of the html to clipboard has been successful? Excel Jeanie does this but I deliberately eschewed the idea just cos it ends up being one more irritant to click an OK button (but maybe that's what everyone wants?).

 

[Sandeep Warrier]

A question for everyone: should there be a warning message box or some such notifying that the copy of the html to clipboard has been successful? Excel Jeanie does this but I deliberately eschewed the idea just cos it ends up being one more irritant to click an OK button (but maybe that's what everyone wants?).

I feel that as long as the data is being copied without any issues, there is no need for a confirmation message. I like using your version of the HTML maker cuz there are less options to choose from - leading to lesser confusion - and no temp files are being created (which Excel Jeanie does)

 

[sanrv1f]

A question for everyone: should there be a warning message box or some such notifying that the copy of the html to clipboard has been successful? Excel Jeanie does this but I deliberately eschewed the idea just cos it ends up being one more irritant to click an OK button (but maybe that's what everyone wants?).

I dont think there is any need for such a pop-up, instead you could add an item like "About HTML maker" to the menu on the Excel main manu bar

 

[yytsunamiyy]

A question for everyone: should there be a warning message box or some such notifying that the copy of the html to clipboard has been successful? Excel Jeanie does this but I deliberately eschewed the idea just cos it ends up being one more irritant to click an OK button (but maybe that's what everyone wants?).

Not having seen your html maker in action yet (just pm'd you), my gut-feeling says that a pop-up is rather annoying - at least it is for me with excel jeanie. However, it should be explained in a help-file or some such that the code is put on the clipboard ready to paste unless you receive an error message, just to save the board a million questions along the lines of "HELP! BOARD HTML MAKER DOES NOT WORK!". When (not if) they come at least we can then say RTFM

 

[sanrv1f]

another option could be, to show a messege in the status bar, for few seconds

 

[Colin Legg]

I agree with you and the others: no need for the pop-up.


A bug to report in the new version.

If I try to generate using a range containing an empty cell then the ReplaceSpace function bugs out with RTE 7 Out Of Memory on this line:

ReplaceSpace = StrConv(b, vbUnicode)

where b is of type Byte(0 to -1)