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!

 

[Richard Schollar]

Sandeep

The code does a check for a cell value being IsNumeric which doesn't pick up dates as being numeric (no idea why) and then right aligns. I could probably place in an extra check so that proper dates get right aligned too. Dates that look like dates but are text may well pass any checks I put in place, however, so I think what Jeff said might well be a good idea (just mimic the spreadsheet). Not sure if that will pick up cells which haven't expressly been given an alignment though - I will have to test.

 

[Colin Legg]

It would be nice if differentiation could be made between a single cell CSE formula and a multiple cell CSE formula, otherwise it could be confusing:
http://www.mrexcel.com/forum/showpost.php?p=1998134&postcount=325

Possible workaround?:
http://www.mrexcel.com/forum/showpost.php?p=1998157&postcount=326

This could also save some rows in the Array Formulas summary box?

 

[schielrn]

Colin, that link wouldn't work for me and I have noticed this with some other links that people have pasted?

 

[Colin Legg]

Thanks, schielrn. Links fixed.

 

[Colin Legg]

I also managed to get an empty formula summary box to appear when I used a CSE formula in a merged cell:

http://www.mrexcel.com/forum/showpost.php?p=1998124&postcount=324

Not really a big issue but thought I'd mention it...

 

[Richard Schollar]

It would be nice if differentiation could be made between a single cell CSE formula and a multiple cell CSE formula, otherwise it could be confusing:
http://www.mrexcel.com/forum/showpost.php?p=1998134&postcount=325

Possible workaround?:
http://www.mrexcel.com/forum/showpost.php?p=1998157&postcount=326

This could also save some rows in the Array Formulas summary box?

Yeah I like that. Great! More work...

 

[Richard Schollar]

I also managed to get an empty formula summary box to appear when I used a CSE formula in a merged cell:

http://www.mrexcel.com/forum/showpost.php?p=1998124&postcount=324

Not really a big issue but thought I'd mention it...

Wash your mouth out with soap!!! You being an MVP and all and using merged cells! Shame on you!

 

[Colin Legg]

Yeah I like that. Great! More work...

lol... knew you were going to like them.

 

[Colin Legg]

For the array range point, I think possibly this:

In the Sub Make_Grid procedure, amend to:

        For Each cell In rngFormulas
            If cell.HasArray Then
                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

In the MakeFormulaTable function:

For Each cell In r
    If cell.HasArray Then
        temp = temp & "<tr><th >" & cell.CurrentArray.Address(False, False) & "</th><td>" & FormatFormula(cell.Formula) & "</td></tr>"
    Else
        temp = temp & "<tr><th >" & cell.Address(False, False) & "</th><td>" & FormatFormula(cell.Formula) & "</td></tr>"
    End If
Next cell

 

[Richard Schollar]

Cheers Colin