# 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!


----------



## schielrn

Richard, I really like it so far. One thing for me that might be nice and not sure what others think, is when you click the Generate html code, is to have a message box or something come up saying that it is copied to the clipboard. But I guess after using it and understanding what happens, this is not necessary.

Only reason I say this, is because I clicked the dang on button like 10 times thinking it was doing nothing and then thought to myself I will try to paste it and voila what do you know it was already copied to the clipboard.


----------



## Richard Schollar

Hi Rob - I think that's probably something for the Guide/FAQ (or maybe a popup tooltip).

Some other amendments that seem like a very good idea are:

1. Re-coding the way the grid is sent to the html output functions to account for the non-contiguos ranges

2. Outputting range name definitions that are contained in formulas

3. Just cleaning up the code because there's a whole lot of constants and stuff that are no longer used and probably need to be removed


----------



## Jon von der Heyden

Point 2 would be REALLY cool to have included Richard!!!


----------



## TheNoocH

Richard,
Like the ability to right click...so much easier than going to the ribbon and clicking the drop downs...

couple things I would like to see
1)  showing the cell colors (whether they are conditionally formatted or just fill colors)
2) gray shading in the row and column sections of the output (similar to Excel Jeanie)...i think it helps break up the sheet so it's easier to read...

only started testing so if i see anything else that would be helpful i'll post back...

is there (or could there) be a section or thread for just posting in some tests?  what i've been doing is posting in the beta sheet into a thread and also posting in an excel jeanie sheet into a thread to see the diffs...then going back to delete one of them...it would be nice to just have a test dumping ground so i didn't need to do all the deleting...thoughts?


----------



## Richard Schollar

Jeff

Here's the testing thread:

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

In response to questions:

1. Conditional Formatting analysis isn't built in to the code.  I have no plans at the moment to do so either (too many other things to do).  However, the code should pick up normal shading of the cells - are you not seeing this?

2. This should already be in place (see the test thread above) - again are you not seeing this?  This may be due to the way Excel 2007 handles colours.  I have not actually tried it on in 2007.  Will have to give it a go.  Thanks for the feedback!


----------



## TheNoocH

don't know what i was looking earlier...but just put a test out in that thread and regular fill seems to work as does the grey shading in the row/column....


----------



## TheNoocH

another thing i noticed...

numbers appear to be left justified...should they not be right justified?

see post 287 on this link

http://www.mrexcel.com/forum/showthread.php?t=387506&page=29


----------



## Richard Schollar

I'll look at including that functionality (right justification of numerics).  Hopefully should be relatively straightforward.


----------



## sanrv1f

Richard,

Im trying the 30-Jun-09 version, and getting 

"User-defiend type not defined"

at



		Rich (BB code):
__


Sub Make_Grid(Optional ByVal generator_setting As FormulaSettings)
    
    Dim rngWhole As Range
    Dim strOutput As String
    Dim objData As DataObject



is this not supposed to work with 2003?


----------



## 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!


----------



## Jon von der Heyden

That would suggest that you are missing Microsoft Forms 2.0 Library...  Although I'm not sure why?


----------



## TheNoocH

this is what Richard sent to me in an email when I had the same problem

You need a reference to the Microsoft Forms 2.0 Object Library  - the easiest way to get this is to insert a userform in the add-in (via the VBE just go Alt+I+U).  This will create the reference and you can then simply delete the userform (as it isn’t required, just the reference).  I suspect that the add-in referenced an earlier version of the library file than you had and so the reference was lost (it may be marked as MISSING when you go Tools>References in the VBE, in which case please uncheck it from the list before you insert the userform)


----------



## Jon von der Heyden

> I suspect that the add-in referenced an earlier version of the library file than you had and so the reference was lost.


 
That explains it...


----------



## sanrv1f

Jon von der Heyden said:


> That would suggest that you are missing Microsoft Forms 2.0 Library...  Although I'm not sure why?





TheNoocH said:


> this is what Richard sent to me in an email when I had the same problem
> 
> You need a reference to the Microsoft Forms 2.0 Object Library  - the easiest way to get this is to insert a userform in the add-in (via the VBE just go Alt+I+U).  This will create the reference and you can then simply delete the userform (as it isn’t required, just the reference).  I suspect that the add-in referenced an earlier version of the library file than you had and so the reference was lost (it may be marked as MISSING when you go Tools>References in the VBE, in which case please uncheck it from the list before you insert the userform)



for some odd reason the add-in removes the reference to Microsoft Forms 2.0 Object Library, and I need to refer it again, manually, to resolve the problem


----------



## Jon von der Heyden

I haven't encountered that (yet)...

But perhaps, does adding and keeping a userform in the addin make any difference?


----------



## sanrv1f

Richard,

There could be some problem with providing spaces in the formula 

see this

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

when the someone copy the formula to their sheet, that might not work


----------



## TheNoocH

Richard,
would it be possible to determine the cell alignment of the cell and output the html in the same format?


----------



## Richard Schollar

sanrv1f said:


> Richard,
> 
> There could be some problem with providing spaces in the formula
> 
> see this
> 
> http://www.mrexcel.com/forum/showthread.php?t=400225
> 
> when the someone copy the formula to their sheet, that might not work



Aahhh.  ****.  Yes that's a problem.  I can always remove the addition of spaces in the formula string which then has the possible problem of the formulas not wrapping.  So, I could check the position of the quotes in the string and not insert any spaces if the characters I currently search on are within quotes (I already do this for brackets so it may not be that difficult).  Everytime I change the functionality there is an unforeseen consequence!


----------



## Richard Schollar

TheNoocH said:


> Richard,
> would it be possible to determine the cell alignment of the cell and output the html in the same format?



SOunds reasonable - I'll look into it


----------



## sanrv1f

few more,


could you make the tool to convert date content to a standard format such as "dd-mmm-yy", I think this could be done in the cell content analysis part (ie) if a cell's format is date then convert the format to standard (http://www.mrexcel.com/forum/showpost.php?p=1987473&postcount=290)
the tool seperates any merged cell and shows the content in the first cell only (http://www.mrexcel.com/forum/showpost.php?p=1987474&postcount=291)
can you include the version of Excel and the OS, as a tag line in the output
just to suggest, can you convert a formulae to standard version (ie) replacing the ";" (used in some non-english verisons) with ","


----------



## 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!


----------



## sanrv1f

Richard

there is some problem with rendering hidden columns

http://www.mrexcel.com/forum/showpost.php?p=1988894&postcount=296


----------



## TheNoocH

Richard...installed the latest beta...now getting TYPE MISMATCH on this line in the Generate_Grid SUB...



		Code:
__


ReDim vGrid(1 To UBound(vRows, 1) + 1, 1 To UBound(vColumns, 1) + 1)


NOTE: seems to work most of the time but getting that error selecting this set of data...

http://www.mrexcel.com/forum/showthread.php?t=387506&page=31
see post 306

which was originated from this thread
http://www.mrexcel.com/forum/showthread.php?t=400792&page=2


----------



## Richard Schollar

Jeff

Not sure why this is happening to you (I can't seem to recreate).  I'm going to send you out the latest version which uses modified code for generating the grid.

Richard


----------



## TheNoocH

RichardSchollar said:


> Jeff
> 
> Not sure why this is happening to you (I can't seem to recreate).  I'm going to send you out the latest version which uses modified code for generating the grid.
> 
> Richard



that seems to have fixed it for me...thanks...


----------



## sanrv1f

TheNoocH said:


> Richard...installed the latest beta...now getting TYPE MISMATCH on this line in the Generate_Grid SUB...
> 
> 
> 
> Code:
> __
> 
> 
> ReDim vGrid(1 To UBound(vRows, 1) + 1, 1 To UBound(vColumns, 1) + 1)
> 
> 
> NOTE: seems to work most of the time but getting that error selecting this set of data...
> 
> http://www.mrexcel.com/forum/showthread.php?t=387506&page=31
> see post 306
> 
> which was originated from this thread
> http://www.mrexcel.com/forum/showthread.php?t=400792&page=2


 



RichardSchollar said:


> Jeff
> 
> Not sure why this is happening to you (I can't seem to recreate). I'm going to send you out the latest version which uses modified code for generating the grid.
> 
> Richard


 

I have the 05 July version, the problem Jeff described occurs when I try to generate HTML for a single row or single column, could you send me the latest version?


----------



## Richard Schollar

will do Sankar.  I must have missed something in the original code - d,oh!


----------



## Richard Schollar

Sankar - I don't have your email address at work - can you PM me again?


----------



## Richard Schollar

1. Is an interesting idea but I'm worried it might confuse some newbie users that the output doesn't exactly match what is on the sheet.  It could potentially be added as an option though

2. I'm inclined to ignore merged cells and not make any special provision for them

3. Excel version is a simple modification, OS is a bit harder and to the best of my knowledge requires API calls.  I'll place the Excel version in the next release, OS will have to wait (and I may not try to include this).

4. Currently only a 'standard' formula should be output (ie using , and not ; and, I believe, using standard English for the function names (not 100% sure about this as I don't have a non-English version to test with)).  It would be possible to include the option to output the FormulaLocal instead (potentially could include a switch for FormulaR1C1 if desired too).





sanrv1f said:


> few more,
> 
> 
> could you make the tool to convert date content to a standard format such as "dd-mmm-yy", I think this could be done in the cell content analysis part (ie) if a cell's format is date then convert the format to standard (http://www.mrexcel.com/forum/showpost.php?p=1987473&postcount=290)
> the tool seperates any merged cell and shows the content in the first cell only (http://www.mrexcel.com/forum/showpost.php?p=1987474&postcount=291)
> can you include the version of Excel and the OS, as a tag line in the output
> just to suggest, can you convert a formulae to standard version (ie) replacing the ";" (used in some non-english verisons) with ","


----------



## Sandeep Warrier

Dates are left aligned by default. Is it possible for the html generator to mimic excel... i.e. right align proper dates and left align text that looks like date??

http://www.mrexcel.com/forum/showpost.php?p=1996351&postcount=9http://www.mrexcel.com/forum/showpost.php?p=1996351&postcount=9


----------



## TheNoocH

sandeep.warrier said:


> Dates are left aligned by default. Is it possible for the html generator to mimic excel... i.e. right align proper dates and left align text that looks like date??
> 
> http://www.mrexcel.com/forum/showpost.php?p=1996351&postcount=9http://www.mrexcel.com/forum/showpost.php?p=1996351&postcount=9



Or Richard would it make more sense to read the alignment of the cell and output it to the html?  then that way the output would appear as your actual sheet alignments?


----------



## 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

Colin_L said:


> 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

Colin_L said:


> 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:


		Code:
__


        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:


		Code:
__


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


----------



## 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:



		Rich (BB 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

RichardSchollar said:


> 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

RichardSchollar said:


> 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

RichardSchollar said:


> 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:


		Code:
__


ReplaceSpace = StrConv(b, vbUnicode)

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


----------



## 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

Ah - I'll fix that right now Colin.  Thanks!


----------



## Richard Schollar

I'll send out another build tonight with the correction required for Colin's point above.  In the meantime, the correction I have put through is the following:

In module mGenerateGrid in the Generate_Grid sub replace this line:



		Code:
__


vGrid(i + 1, j + 1) = Replace(ReplaceSpace(ReplaceChar(ar.Text)), Chr$(160), " ") 'replace < and > and also spaces

 
with this line:



		Code:
__


    If Len(ar.Text) > 0 Then
        vGrid(i + 1, j + 1) = Replace(ReplaceSpace(ReplaceChar(ar.Text)), Chr$(160), " ") 'replace < and > and also spaces
    End If


----------



## yytsunamiyy

Is it a known issue that your html maker is not picking up Formatting of conditionally formatted cells on default settin gin XL07?

see here: http://www.mrexcel.com/forum/showthread.php?p=2003747#post2003747


----------



## Richard Schollar

Yes - I decided to ignore Conditional Formatting (Data Validation too).


----------



## Sandeep Warrier

Is the 12 column limit a temporary or a permanent feature? Cuz it's possible that even sample data may have more than 12 columns


----------



## taigovinda

I'm a total novice and am enjoying using this tool to help me ask my questions 

It seems to slow up Excel quite a bit sometimes, though, when I'm trying to exit.  I don't know enough to understand why this add-in needs to save itself.  I'm wondering, will it hurt anything if I comment out these two lines?



		Code:
__


Private Sub Workbook_BeforeClose(Cancel As Boolean)
CleanUp
----->On Error Resume Next  'in case workbook opened read-only
----->ThisWorkbook.Save
End Sub


Thanks,
Tai


----------



## Richard Schollar

I put that in so the default setting was saved when excel was shut down.  you can safely remove that sub altogether though with very minimal impact.


----------



## TheNoocH

***EDIT*** not sure why HTML isn't showing properly???

Richard...looks like one of the updates messed up the alignment on numbers...

These are regular numbers entered into the cell (ie looks like right aligned in Excel...NOTE: but i did not choose right aligned)Excel WorkbookC1121031004100051000061000007100000Sheet2*Excel 2007*


This is the same data but i clicked on center aligned...Excel WorkbookA1121031004100051000061000007100000Sheet2*Excel 2007*

This is same data selecting right aligned...Excel WorkbookE1121031004100051000061000007100000Sheet2*Excel 2007*


PS...I tried to go to the Testing link and it says I don't have authority...did something change...I was able to get into it before...


----------



## Joe4

Nooch,

Just wanted to let you know that I moved your last "stand-alone" test to the "Test Here" forum.


----------



## TheNoocH

Joe,
thanks...i was just looking for it and thought i was losing my mind....

Richard...
here is a link to the html in the above post (#58)...once again not sure why didn't show properly above...
http://www.mrexcel.com/forum/showthread.php?t=405525


----------



## 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

html is disbled in posts in this forum 

A new build has been dispatched with code correction as suggested by Jeff.  Now, if the horizontal alignment of the cell has not been set, the code checks to see if the cell contains a numeric value - if so, it will right-align the cell contents.


----------



## TheNoocH

html disabled then makes sense to why it didn't show...

i posted another test and comments in that testing thread...

thanks again...


----------



## Sandeep Warrier

If the cells use currency formatting, the formatting at times goes haywire.

http://www.mrexcel.com/forum/showthread.php?p=2013096#post2013096


----------



## Richard Schollar

That's interesting Sandeep - I have an idea why that may be (it isn't curreny formatting per se, it is the fact the format probably has "* " element in it.  When I then use the Text property to return the information from the cell, it converts these values to proper spaces which then get replaced with non-breaking spaces which in turn causes problems.  I think I can correct this, but it may take me a couple of days (work has gone a bit crazy keeping me away from the Board & Excel).  Thanks for this though - very helpful


----------



## TheNoocH

Richard...is there something special i have to do to have the Range Names show up?
see my post #4 in this thread...i have named ranges as manually typed in...but they arent' showing up in the html output...

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


----------



## Richard Schollar

Jeff

You don't have any actual names in your formulas as you are indirectly referencing them - the code only identifies names to include by looking at the formula string (ie =INDEX(INDIRECT(A1),...) - all it sees in your formulas is the INDIRECT(A1) - it (or I!) wasn't smart enough to determine that this refers to a ranged name.


----------



## TheNoocH

wasn't sure if it looked for named ranges in the range of cells selected...but since it's based on the formulas then i understand why they didn't show...thanks


----------



## taigovinda

Hi,

When I right-click, I see the three normal options for the add-in.  But I see them repeating many, many times on my work computer.

I tried on my home computer and it seems like each time I enable (after disabling) the add-in, it adds another occurrence of the three options.  How can I get rid of all the extras?

Thanks,
Tai

Edit:  also, are the new builds posted somewhere?  Thanks.



> A new build has been dispatched


----------



## Richard Schollar

Ah have I not been sending you the new builds?  I meant to include everyone but I may have missed you 

The latest version is *HtmlMaker20090730a.xla* and it should deal with the issue you describe.  Please could you give me your email address via PM and I'll send it out to you.


----------



## yytsunamiyy

Hi Richard, I encountered an odd behaviour today:
"<" sign was interpreted as html sign for some reason and formula text did not show. 

example:
http://www.mrexcel.com/forum/showpost.php?p=2029252&postcount=6

b.t.w.

I haven't received release *HtmlMaker20090730a.xla* either. I'll PM you my address again.

Regards,

Stephan


----------



## 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.


		Code:
__


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

RichardSchollar said:


> 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

Colin Legg said:


> 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

snowblizz said:


> 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:


		Code:
__


    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 
	
	
	
	
	
	




		Code:
__


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


----------



## 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!


----------



## yytsunamiyy

Hi Richard,

No critique, just a question:

Would it be possible to include an option to return the selected Range as text only?
Some boards, XING in particular, only allow text-entries. If you want to show a table it has to have the format

...A.....B....C
1.C.....1.....x
2.B.....3.....y

and so on. XING and other pure text-boards tend to dump tabs and spaces.

Bonus question: Can you include an option to output formulas in the native-language of the system via Range.FormulaLocal rather than Range.Formula?

Edit: COOOOOOOL - this is post 666 - PURE EVIL! HA HA HA HAHAAAAAAAA!


----------



## Richard Schollar

The formulalocal wouldn't be difficult but I don't follow on the first question - the add-in is only meant for use on MrExcel.com (it references css styles contained in the forum code so it will never be fully portable (with all formatting) to other forums that do not have these codes embedded in their css.  I don't really understand what you mean when you say to return as text only as being html it is only being returned as text?


----------



## yytsunamiyy

RichardSchollar said:


> but I don't follow on the first question - the add-in is only meant for use on MrExcel.com (it references css styles contained in the forum code so it will never be fully portable (with all formatting) to other forums that do not have these codes embedded in their css.


 
Sorry, I didn't realise that. In that case there is no point in trying to make it portable. I actually didn't want to preserve formatting - that's a can't do anyway in ASCII-only forums.



RichardSchollar said:


> I don't really understand what you mean when you say to return as text only as being html it is only being returned as text?


 
I meant to get the result as if you copy a range from XL to the Windows-Editor, manually replace the tabs with "." to preserve spacing and copy the resulting text to the board in question, so instead of

(imagine the actual table rather than html showing here )Excel WorkbookABC1c1x2b3yTabelle1


you get

...A.....B....C
1.C.....1.....x
2.B.....3.....y



RichardSchollar said:


> The formulalocal wouldn't be difficult


If the html maker is only for Mr. Excel there isn't much point in implementing - after all, there isn't that much traffic in "Questions in other Languages" where it might come in handy.


----------



## sanrv1f

del


----------



## sanrv1f

deleted


----------



## yytsunamiyy

Hi Richard, even with build 20090730a the problem with "< " seems to persist. See here: http://www.mrexcel.com/forum/showpost.php?p=2043069&postcount=17


----------



## snowblizz

When I close Excel I get the following "crash". Runtime error "5" here (last line):


		Code:
__


Sub CleanUp()
    Dim cbc As CommandBarControl
    On Error Resume Next
    Set cbc = Application.CommandBars("Worksheet Menu Bar").Controls("MrExcelHtml")
    If Not cbc Is Nothing Then
        Do
            cbc.Delete
            Set cbc = Nothing
            Set cbc = Application.CommandBars("Worksheet Menu Bar").Controls("MrExcelHtml")     <--- here

Furthermore, it leaves the menuoptions intact, I now have 6 copies of the menu when I right click! As it creates a new one each time Excel starts.
EDIT:
It also crashes whenever part of the selection has text in the formula.
Or if I select empty formulas, it crashes, and when stopping the debugging it pops a Msgbox saying "nothing found".


----------



## Richard Schollar

yytsunamiyy I will check out the code - that looked like an array formula and it may be the case that these aren't handled correctly.

Snowblizz - if you open up the VBE and go Tools>Options>General tab under Error Trapping what option do you have selected?  You should have "Break on Unhandled Errors" and *definitely not* "Break on All Errors".


----------



## yytsunamiyy

actually, the problem didn't occur in the array-formula, but in the normal 


=IF(MATCH($B$1,'List of Values'!$A$1:$A$4,0) < MATCH($D$1,'List of Values'!$A$1:$A$4,0),$D$1,$B$1)

with " < MATCH(" missing.


----------



## Richard Schollar

Hmm, I'll have a look at the code but that shouldn't be happening - can you send me the worksheet that holds the formula where this problem occurs (remove any other sensitive info, just make sure the problem exists when you generate the html) - thanks!


----------



## 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!


----------



## yytsunamiyy

Hm, it was a wb in XL07 made up on the spot that just served to help the OP, so I never saved it. I'll try to replicate the problem on Monday when I'm back at work and at the same computer and will send you the wb if sucessful.


----------



## snowblizz

RichardSchollar said:


> Snowblizz - if you open up the VBE and go Tools>Options>General tab under Error Trapping what option do you have selected?  You should have "Break on Unhandled Errors" and *definitely not* "Break on All Errors".


That did indeed "fix" that problem.


----------



## yytsunamiyy

Strange - I can't replicate the error - at least not in the test-forum. If it occurs again I'll save the file.


----------



## sanrv1f

another one,

I have both XL 03 & 07 open (with HTML maker Add-in included), If I close the 2007 first (not just file, the instance), then it saves a copy of "HTML Maker.xls" in the folder where the file opened in 2007 located, 

If I have 2 instances of XL 03, and I close one, it saves "HTML Maker.xla" in the folder


----------



## sanrv1f

The tool generates the list of all names defined in the workbook, even when, they are not referred in the formula


----------



## Richard Schollar

Sankar

That should happen only in certain circumstances eg if the following names exist in the workbook:

Name1
Name12
Name123
Name1234
Name12345
Name123456
Name1234567

Then, if Name1234567 is in a formula being output by the html maker, all the others will be included as well in the html output. However, if only Name1 is in a formula being output by the html maker, then none of Name12, Name123 etc should be output.

Entirely different names not referenced at all in any formulas being output by the html maker shouldn't be output in the html.

Please confirm that this is what you are seeing.


----------



## Sandeep Warrier

Richard,

I tried using the option Generate HTML -> 1st cell only.

I had formulae in cells C3:D6, however the HTML displayed only the formula in C3.

Is it possible to modify the code to display formulae from 1st cell of each selected column?

(I edited the HTML and used the default setting)
http://www.mrexcel.com/forum/showthread.php?p=2055935#post2055935


----------



## Richard Schollar

Hi Sandeep

Did you try using the "First cell in each column" option?


----------



## Sandeep Warrier

LOL.....

real embarrassing... dunno how I missed it 

Effects of handling 4 projects at the same time I think...


----------



## xenou

I've been finding this adding very useful ... is it nearing "version 1.0" release for others?  Seems great to me - whether or not some bugs remain.


----------



## 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

There are a few fixes that I need to incorporate (most pressing is a problem with the user-defined formula selection if only one cell is selected - I have the fix, just need to roll it up in a release), plus the order in which formulas are outputted in the html (think this will be relatively easy to apply) and a somewhat more difficult one which deals with range names (mentioned a couple of posts ago in response to Sankar's query).  There are probably others too, but these are the ones I will concern myself.  I am also going to change the UI so that it doesn't occupy its own spot on the menu bar but is just part of the Tools menu.

Get that done, and I think it will be more or less ready to go on general release (so that people can go to a download location rather than having to PM me to get it).

Oh, and of course I have not made any xl2007 specific UI changes.  So that (and 2010) will no doubt be next on the agenda...


----------



## drhatmrexcel

I am interested in your new version of html maker so that I can post to the mrexcel forums.

Thank you,
Dennis


----------



## goofy78270

I just started using this tool and the first thing that comes up is that you do not have the ability to set a range manually other than highlighting it.  I would like an ability to edit the range as done in  Excel Jeanie HTML 4 .  
This would help greatly when users have merged cells that extend past the 12 row or 30 column limit.

In addition, is there a reason for the limit to column and row size?


----------



## Richard Schollar

No it's a totally arbitrary limit meant to prevent ridiculously large posts.  It's easy enough to change in the code if you know what you're doing (there are two public constants in the mSettings module which define these limits:



		Code:
__


Public Const MAX_ROWS As Long = 30
Public Const MAX_COL As Long = 12

 
You'd just need to amend these values.


----------



## goofy78270

As there are surely many other settings that be modified through the project, many users may not know how to access them or be timid in changing them, hence the prompt option to edit the range of selected cells.

In addition, I would like to see a user form that allows the user to edit desired setting such as max row or column counts, showing of borders, copying of cell colors, and such.


----------



## Sandeep Warrier

Hi Richard,

Is it possible to include another option for displaying formulae? Something like "Formula from specified cells/1st cell of specified columns"

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

In the thread, formula H2, I2 & J2 is the same (with only references changing), but since I wanted to display formula from G2 too, the "First cell in each column" option gave formulae from all the 4 columns resulting in unnecessary usage of space.


----------



## Peter_SSs

sandeep.warrier said:


> Hi Richard,
> 
> Is it possible to include another option for displaying formulae? Something like "Formula from specified cells/1st cell of specified columns"
> 
> http://www.mrexcel.com/forum/showthread.php?p=2141335&posted=1#post2141335
> 
> In the thread, formula H2, I2 & J2 is the same (with only references changing), but since I wanted to display formula from G2 too, the "First cell in each column" option gave formulae from all the 4 columns resulting in unnecessary usage of space.


Can't you use this?
Generate Html (specify option)|User Defined Selection
then selct the cells/ranges you want with Ctrl+click/drag


----------



## Sandeep Warrier

Peter_SSs said:


> Can't you use this?
> Generate Html (specify option)|User Defined Selection
> then selct the cells/ranges you want with Ctrl+click/drag



I want to display all info and formulae only from 1st cell in 2 columns. I'm not sure if this is possible through the "User Defined Selection" option.


----------



## Peter_SSs

You can. All cells in row 1 here have a similar formula, but I have just shown 2 of them using the method I described.


----------



## Sandeep Warrier

Ohh, cool!!

I misinterpreted the meaning of user-defined selection.


----------



## 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

Guys

There is a problem with User Defined Selection if you only select a single cell and there are more formula cells in the originally selected area being output:  Excel will output not just the single cell but *all* formula cells.  I have the correction for this but haven't rolled it out yet.


----------



## hardeep.kanwar

Hey Richard Schollar

I Want this HTML MAKER for Posting My Queries in this Forum, but i am Unable to  Get the Link

Could you Pls Provide me the Link, Where i can Download this HTML MAKER Easily

Thanks in Advance

Hardeep Kanwar


----------



## Sandeep Warrier

Hi Richard,

In the thread

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

the values in Sheet3 against 3 & 9 were CHAR(23) and CHAR(6) respectively but as can be seen, its not showing correctly.

I'm not sure if I should post this in the About the Board section or here....


----------



## Richard Schollar

Hi Sandeep 

That could well be because Ascii 23 and 6 are non-printing characters and have no defined appearance (as far as I am aware).


----------



## Sandeep Warrier

RichardSchollar said:


> Hi Sandeep
> 
> That could well be because Ascii 23 and 6 are non-printing characters and have no defined appearance (as far as I am aware).



Oh all right.

I understand when you say they have no defined appearance. ASCII 23 showed as a T turned to its side while 6 showed as a hyphen on my office computer while they show up as small squares on my home computer.


----------



## Phixtit

You have PM RichardSchollar
Thanks.


----------



## Sandeep Warrier

Hi Richard,

Doesn't the HTML Maker take in variations in formatting within a cell?

For example, in the thread http://www.mrexcel.com/forum/showthread.php?p=2171571#post2171571

I formatted the string in the cell as Bold for the numbers and Bold+Italic+Red Font Color for the text. But the HTML Maker output gave only bold format.


----------



## InaCell

Hi Richard

Thanks for sending copy through and the work you have put into this code.

I have copied resulting html code into browser and everything is displaying correctly.

When I copy into the forum, the borders around my cell selections only show for the right and bottom. There must be something in the css of the site causing the left and top border to disappear.

Could this please be looked at.

Cheers, InaCell.


----------



## Richard Schollar

What browser are you using?


----------



## InaCell

Ie7


----------



## 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!


----------



## InaCell

Richard 

Here is example HTML code as pasted.



		HTML:
__


[RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]O[/XH][XH]P[/XH][XH]Q[/XH][/XR][XR][XH]37[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]38[/XH][XD][/XD][XD=h:c|fw:b]Full NAME[/XD][XD][/XD][/XR][XR][XH]39[/XH][XD][/XD][XD=h:c|fs:i]Event 1[/XD][XD][/XD][/XR][XR][XH]40[/XH][XD][/XD][XD=h:c|fs:i]Event 2[/XD][XD][/XD][/XR][XR][XH]41[/XH][XD][/XD][XD=h:c|fs:i]Event3[/XD][XD][/XD][/XR][XR][XH]42[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]with dates (2)[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2003[/B]


----------



## Richard Schollar

I can't review the css of this site I'm afraid (don't have access to it other than by reviewing the css as downloaded to the browser).  I remember suffering a similar problem with borders but that was using Firefox (rendered fine in IE though).  I will take a look at what is being output by the code and see if there are any amendments I can make to fix said problem.

Thanks for bringing it to my attention!


----------



## Fowmy

Hi Richard,


I really like your addin so far, very convenient too.

But I have a problem, I am not sure this is because I  use Excel 2010, when I right click on an Excel Table, the HTML Maker options are not available in the shortcut menu, I have to reach the ribbon|Add-ins. 

Has this been already addressed or is it the HTML Maker not updated for 2010 ?


----------



## Richard Schollar

Are you trying to generate html output for cells which are part of a pivot table?  The pivot table takes over the right-click "Cell" menu thus subverting the functionality in the html maker.  If not, is there anything else unusual about the excel range eg is it part of an external data range or an Excel Table?


----------



## Fowmy

Its part of an excel table


----------



## robgrant

Richard: One of the other moderators on this board suggested I explore this link before posting for help with various excel problems. I'm not sure what this is all about, but when I try clicking any of the example links mentioned in this post, I'm told that I do not have permission to access the link. Can you please direct me to more information?


----------



## bbbuffalo

Will this also support sorting and filtering? If so I NEED this. How can I get it?


----------



## Richard Schollar

Hi

You can just PM me your email address and I will send it to you.

When you ask will it support sorting and filtering can I ask what you mean?  It will ouput a filtered list and just output the visible cells if that's what you meant.


----------



## robgrant

Richard:

You already sent me your program. Apparently it doesn't play nice with the combination of Excel 2007 and Windows 7 64-bit.


----------



## Richard Schollar

Hi Rob

I think the correction i tried to apply last week failed because of an intermediate step using xl2010.  I have corrected using xl2002 at work, but I do not have your email address at work so would you mind PMing me your email and I will send the new version on to you?


----------



## 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!


----------



## RoryA

Rich,
Can you send me the current version too? I seem to have lost my copy...


----------



## Richard Schollar

Sure thing


----------



## Jon von der Heyden

Me too please boss...  I have an older version.


----------



## Richard Schollar

No probs Jon - PM me the email address you want me to use.  Have you got full internet up & running yet, or are you still at your bro's?


----------



## Colin Legg

Me too please boss.


----------



## Jon von der Heyden

Richard Schollar said:


> No probs Jon - PM me the email address you want me to use.  Have you got full internet up & running yet, or are you still at your bro's?



Still at my bro's home.  Seems like we times the move all wrong.  We are deterimed to live in the same security estate as my father and sister but there's just about nothing available to let.  Well there is but only enormous homes that would set me back more than what I was paying in the UK.   The sort of size that we would rent is more than likely being let to students.  Students from this town tend to come from very wealthy families.  But they should all bugg3r off around November so we should find something then.

I have internet at my bro's but I don't have my own office.  I was planning on using my brother-in-laws office but I can't leave the girls on their own here at the moment (in the sticks).  And I really need an office, I can't get anything done with my little girl at my feet all day long.


----------



## robgrant

Rich:

This version passes the immediate installation test. You need to change your installation instructions for this version. though.

I placed a test on the test board.


----------



## Rekd

Richard Schollar said:


> The output is fairly consistent with that produced by Excel Jeanie as can be seen here:
> 
> http://www.mrexcel.com/forum/showpos...&postcount=241


 
I don't have access to that page either, but I'm pretty sure it's what I think it is. If this project outputs BB then I'm definately interested. PM sent.


----------



## Sandeep Warrier

Hi Richard,

Which is the current version of the HTML Maker? I'm using HtmlMaker20090730a.....


----------



## Richard Schollar

That's the latest at the moment Sandeep


----------



## 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!


----------



## bbbuffalo

Richard Schollar said:


> When you ask will it support sorting and filtering can I ask what you mean?  It will ouput a filtered list and just output the visible cells if that's what you meant.



What I mean is will I be able to sort and filter the HTML on the webpage like I am able to do in Excel?

For instance if I have the following fields

Book Title, Author, Genre, Date

Could I filter to see only the Sci-Fi genre and then sort alphabetically by Title.


----------



## Richard Schollar

No - all the html maker does is output a representation of your visible excel data that you select.  All sorting and filtering must be done prior to using it.


----------



## spanspace

How well does this work with Excel 2010? I just emailed myself the last file you sent me last Sept. If there is a newer version let me know.


----------



## Richard Schollar

It works fine with 2010 - although there is a small modification required if using a 64bit version of Windows.  There is no newer version of the file.


----------



## yytsunamiyy

Hi Richard,

thank you for sending the file over. For some reason it bombs out with:

Runtime Error '429':
Objectgeneration by ActiveX-Component not possible

(Error translated from German)

Debug shows the error at Line:



		Code:
__


Sub Generate_Grid(ByRef vGrid As Variant, ByVal rngWhole As Range)
Dim vColumns As Variant, vRows As Variant
Dim dic As Object
Dim i As Long, j As Long, k As Long
Dim ar As Range
[COLOR=red][B]Set dic = CreateObject("scripting.dictionary")[/B][/COLOR]
'first determine columns:
With dic
    For Each ar In rngWhole.Areas
        For i = 1 To ar.Columns.Count
            If Not .exists(ar.Cells(1, i).Column) Then .Add ar.Cells(1, i).Column, ar.Cells(1, i).Column
        Next i
    Next ar

 
Running XL 07 on Vista Business SP2 32Bit


----------



## Richard Schollar

If you go Tools>References in the VBE can you see "Microsoft Scripting Runtime" listed there?  If not, think you will need to install/register this ActiveX control.


----------



## yytsunamiyy

Installed and registered "Microsoft Scripting Runtime" (Scrrun.dll), Problem persists  - Do I need any ActiveX references?


----------



## schielrn

When you have some time can you e-mail me here with the latest version I think the last one I got was late last year or early this year.

Thanks.


----------



## Richard Schollar

It hasn't been changed since then Rob 

I can email you the file if you need it again - let me know.


----------



## yytsunamiyy

While you are here Richard - is there anything else/other I should do than setting up the "Microsoft Scripting Runtime"? It still won't play nice and stops at the same old line...


----------



## 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!


----------



## schielrn

Richard Schollar said:


> 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.


----------



## schielrn

Is there a limit on how many columns you can use even if columns are hidden?

See here:

http://www.mrexcel.com/forum/showpost.php?p=2415182&postcount=3

As the list I am usiong appears below my data set, even though it should be in column Y and I have hidden M:X.

Just seemed weird to me.

Thanks.


----------



## p45cal

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.


----------



## Richard Schollar

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.


----------



## schielrn

Richard Schollar said:


> 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.


----------



## p45cal

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



schielrn said:


> 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.


----------



## schielrn

I guess so, but the version I have does not have that, so i must be a little outdated on which version I have.


----------



## p45cal

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 > ?


----------



## schielrn

p45cal said:


> 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?


----------



## p45cal

I made the changes to the code, unfortunately, the site seems to strip those extra spaces too on the second preview. Grrr.



schielrn said:


> 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?


----------



## 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!


----------



## schielrn

That sucks.  There doesn't really seem to be anything we can do then.  I posted it and then went to edit ti and it was fine, but never tried to do a second preview yet.  I have to go now, but I will try to look at it tomorrow when I am back in.


----------



## p45cal

upon further investigation, when the htmlmaker displays in a browser the spaces were already gone. Maybe there is something elsewhere in the code that strips spaces?
When I do the first preview when preparing a message, then add spaces manually either side of the < character, it survives subsequent (multiple) previews and the submit.
I'm not au fait enough with the code to know quickly how to adjust.


----------



## p45cal

Wrong, wrong, wrong. I was using the wrong html maker! It does work.

So the code which appears to work is:


		Code:
__


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




p45cal said:


> upon further investigation, when the htmlmaker displays in a browser the spaces were already gone. Maybe there is something elsewhere in the code that strips spaces?
> When I do the first preview when preparing a message, then add spaces manually either side of the < character, it survives subsequent (multiple) previews and the submit.
> I'm not au fait enough with the code to know quickly how to adjust.


----------



## schielrn

Glad that worked for you.  I wasn't seeing where it wasn't working and then I looked back at this thread and seeing you said it did work.


----------



## Richard Schollar

Hi All

There is a new version out which principally deals with the problem of the formula boxes extending off the page and being a real pita.  Also addressed was:

- a problem of multiple Cell menu items added if you subsequently opened the html maker again.
- Single cell selection when User Defined Selection was chosen resulted in all formulas on the sheet being transcribed into the html
- increase in number of rows/columns in output (up to 100 rows/columns now)
- all cells in generated grid now get formatted (although number of formats available for output hasn't changed yet)
- *potentially* 64bit Excel/Windows problem has been addressed (I haven't tested this yet as I only have 32bit environments)

If anyone wants the update, please drop me a PM with your current email address.  I am looking to get it hosted in the near future so you can download it directly rather than having to PM me to get it emailed to you.

Happy New Year!


----------



## RoryA

Yes please.


----------



## Richard Schollar

Rory, have sent to your gmail and gmx addresses.


----------



## Sandeep Warrier

Hi Richard,

I'm using the last version (20090730A) of the HTML Maker on the 64-bit version. Got it to work after making a couple of changes in the API declarations.

The one area I face problems is, if a single cell is selected in the User Defined section, it acts like the "All Formulas" option.


----------



## Richard Schollar

Hi Sandeep

I've sent it across - it will be very interesting to hear if the APIs work under Win64.  What I have may only work if Excel 2010 is installed...


----------



## RoryA

You're a gentleman and a Schollar.


----------



## 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

Hi Richard,

After including the APIs in the conditional compilation, everything seems to work fine. I tested it in 2003,2007 (with Win XP) and 2010-64b (with Win7-64b)


----------



## Richard Schollar

Excellent - many thanks Sandeep


----------



## Sandeep Warrier

You're welcome 

All I need to do now is get the VBHTML Maker to work. Have not yet been able to figure out where the problem lies. I get past the API and come up with an error that says object not supported, but does not give a debug option to take me to the problem area


----------



## Richard Schollar

OK I'm going to look at incorporating Conditional Formatting and Data Validation in the output.  It will likely only be for xl versions of 2007 or greater due to me only having xl 2010 on any of my home computers now and imminently due to lose xl 2002 on my works computer.

I will try and make it as fully functional as possible.

Also, something which Alan (al_b_cnu) requested is that when constants (text and numbers) are formatted, their colours are as per they appear in the cell (currently, only whole cell font formatting is applied, so words like "what" will appear as "what").  I will probably look at this last as it will (hopefully) be much easier than doing the CF/Validation.


----------



## Sandeep Warrier

Hi Richard,

I don't think this is a very serious issue, but, on posting the first time all data seems to be in place. But on editing the post, the sheet names go to the center of the page.

See http://www.mrexcel.com/forum/showthread.php?p=2572803&posted=1#post2572803


----------



## Richard Schollar

Hi Sandeep

I think that's a product of the way the Board works rather than an issue with the code generated.  I had noticed it before - it's like the board seems to remove some of the html wrapping the sheet name when the post gets edited.


----------



## Peter_SSs

I mostly use Excel jeanie and posts using that also go a bit strange when edited (blanks get replaced with asterisks). Again I believe it is a board issue not an Excel jeanie issue. When you edit a post, your HTML code is generally still on your clipboard and I am just in the habit of pasting over the code again when I am editing a post.


----------



## Sandeep Warrier

Thanks for the reply Richard. I made a post a few days back with a simple VLOOKUP solution, and the formula box spanned most of the page width. I've not seen this happening with others posts and so I'm not sure if its something to do with my Excel version.


----------



## Richard Schollar

The new version (ie newer than July 2009) should not have the formulas spanning over many screens - they should all be contained within the active browser window area.  This is in contrast to the old version of the maker.  If you do notice this happening with the new one, please provide a link to any posts/threads so I can investigate and - touch wood - resolve.  Thank you


----------



## Sandeep Warrier

It is not spanning over several screens.

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

I thought that the formula box would span the width of the formula, and if the formula crossed a certain length, would wrap the formula to contain it within the screen width. In this case, the formula is just a basic VLOOKUP, but the box still spans most of the screen width.


----------



## 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

Oh i see 

That is a product of me seeting the width property of the table that holds the formulas.  I could remove this setting and then it would work effectively as you stated.  The reason why I didn't originally do this is if you have both worksheet formulas and array formulas (and maybe defined names too) all the tables that contain them would potentially be different widths (which I didn't think looked very good) so I decided to standardise the lot.

I am open to differing viewpoints on this though and there are many fewer occasions when there will need to be worksheet and array formula boxes rather than just one or the other so if people feel strongly I can change this.


----------



## Sandeep Warrier

Ohh OK! Cool 

I'm not sure how feasible this is, but how about having the table width correspond to the width required for the longest formula being used?


----------



## Richard Schollar

Yeah I think I might be able to do that if I nest all the formula tables within an overall containing table - I'll give that a try and report back.  Good feedback - thanks


----------



## Sandeep Warrier

Hi Richard,

There seems to be a problem if someone uses the option "First cell in each column" and if the number of rows containing data is not equal in all columns containing formulae. (Not sure if this makes sense )

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


----------



## Richard Schollar

****! That shouldn't happen - thanks Sandeep, will have to review the code and replicate to discover why it's doing that.


----------



## virtech2k3

Testing


----------



## skyking

Hi Richard,

While I install "HtmlMaker20090730a.xla" I got "Compile error in hidden module: mCreateHTML". I am running Windows 7 and office 2010.


----------



## Peter_SSs

skyking said:


> Hi Richard,
> 
> While I install "HtmlMaker20090730a.xla" I got "Compile error in hidden module: mCreateHTML". I am running Windows 7 and office 2010.


Follow the link in my signature block and I think you will find the latest version.


----------



## skyking

Thank you Peter,

HtmlMaker20101230 works for me. However, VBHTMLMaker doesn't. It give me "Compile error in hidden module :modMain". Any other solution?


----------



## Peter_SSs

skyking said:


> However, VBHTMLMaker doesn't. It give me "Compile error in hidden module :modMain". Any other solution?


At what point do you get that message? I am using VBHTML maker sucessfully with Windows 7 and Office 2010.


----------



## 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!


----------



## skyking

After adding HtmlMaker20101230, I using same procedure to install VBHTMLMaker and that error message pops up. It just like when I install HtmlMaker20090730 unseccussfully.


----------



## Peter_SSs

skyking said:


> I using same procedure to install VBHTMLMaker


Is it this procedure you are referring to? If not, please *detail the steps *you are taking and identify the point at which you are getting the message.


> Installation
> 
> 1.	Download the file (a standard .xla add-in file) and place it in your add-ins folder or a directory location of your choice (add-ins folder on my PC is located at:
> 
> C:\Documents and Settings\Richard\Application Data\Microsoft\AddIns
> 
> 2.	Open Excel and go to Tools>Add-Ins and place a check next to the MrExcelHtml file (if you don’t see it in the list you will need to click on Browse and locate the file manually).
> 3.	That’s it!  It should be ready to be used now, and should be available every time you re-open Excel until you uncheck it from the Add-Ins list.



Since the installation, have you exited right out of Excel and re-started it?


----------



## skyking

Peter,

Thanks for your patient. I have did it the same way as you said. I put the file in
C:\Program Files\Microsoft Office\Office14\Library

Open Excel and go to Tools>Add-Ins, Browse and locate the file manually. After that, the error message pops up with "Compile error in hidden module: modMain". I think when it place a check next to VBE HTML Maker then the error message pops up.

When I un-check it, the error message pops up three time.

That is all I can tell.


----------



## Peter_SSs

skyking said:


> Peter,
> 
> Thanks for your patient. I have did it the same way as you said. I put the file in
> C:\Program Files\Microsoft Office\Office14\Library
> 
> Open Excel and go to Tools>Add-Ins, Browse and locate the file manually. After that, the error message pops up with "Compile error in hidden module: modMain". I think when it place a check next to VBE HTML Maker then the error message pops up.
> 
> When I un-check it, the error message pops up three time.
> 
> That is all I can tell.


I'm not sure what is causing that. If you'd like to PM me with your email address I could send you the file I have been using just in case the one you have is corrupted. Otherwise, I'll have to ask around to see if anybody else has any clues about that issue.


----------



## AHALEY

Are there any solutions for Mac Computers?  I'm running Office 2011.

Thanks


----------



## Smitty

Welcome to the Board!

I'm not sure if it's ever been tested on Mac.  I might have one at my disposal in a few weeks, so I'll make sure to check then.  Until then maybe one of the Mac users can pipe in.


----------



## Peter_SSs

AHALEY said:


> Are there any solutions for Mac Computers?  I'm running Office 2011.
> 
> Thanks


I don't use a Mac but I did ask among the MrExcel MVPs a few months back about this and got no response. Since I know a few of the MVPs use Macs, I'm assuming the lack of response means that they haven't found (or perhaps haven't looked for) a suitable method of posting screen shots.


----------



## sugrue

I am running Excel 2010 and have installed VBE HTML Maker as an Add-in (I just downloaded it from the SkyDrive site it shows last updated Mar 7, 2011 by Chris Smith).

It shows as an Active Application Add-in in Excel Options.  However, it did not add anything to the ribbon and I do not see any new options when I right-click.  I have restarted Excel and no differences.

Any suggestions?

Jack


----------



## Smitty

The VBHTML Maker is for copying VBA code.  If you open the VBE you'll see it under Tools, but it won't appear on the Ribbon.
HTH,


----------



## sugrue

Thanks Smitty.  I see now that I had the 2 add-ins confused.  They are both working now.


----------



## 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!


----------



## bmoon

I have installed the add-in and upon starting Excel 2007, I get a warning message (box with a red "X") with the title: *VB HTML Maker* that states: *Access to the VB project is not trusted*


----------



## Peter_SSs

bmoon said:


> I have installed the add-in and upon starting Excel 2007, I get a warning message (box with a red "X") with the title: *VB HTML Maker* that states: *Access to the VB project is not trusted*


To change that setting ..

Office icon at top left|Excel Options|Trust Center|Trust Center Settings...|Macro Settings|check the box: Trust access to the VBA project object model|OK

However, your message relates to the HTML Maker for displaying vba code whereas this thread was (originally at least) about Richard's HTML Maker for showing worksheet screen shots. Just checking that you are trying to install the correct one.


----------



## bmoon

Peter_SSs said:


> To change that setting ..
> 
> Office icon at top left|Excel Options|Trust Center|Trust Center Settings...|Macro Settings|check the box: Trust access to the VBA project object model|OK
> 
> However, your message relates to the HTML Maker for displaying vba code whereas this thread was (originally at least) about Richard's HTML Maker for showing worksheet screen shots. Just checking that you are trying to install the correct one.



Perfect. Thank you Peter.

I didn't mean to hi-jack the original post. I installed both add-ins. The HTML Maker and the VB HTML Maker.


----------



## Peter_SSs

bmoon said:


> I didn't mean to hi-jack the original post. I installed both add-ins. The HTML Maker and the VB HTML Maker.


I don't think you have hi-jacked the thread as I suspect both HTML Makers have already been discussed. With 200+ posts, I'm not going back to check though!


----------



## johndowd

I installed the MrExcel HTML in my Excel 2011 for Mac and can only get an error code when I try to use it.

After I have defined the range I'd like to copy, I get the following error:

Run-time error '53':

File not found: user32

Not sure what I'm doing wrong here, but I followed the instructions.  It does not make a difference using any of the options, I get the same error.

Thanks.


----------



## Jon von der Heyden

I don't believe that this addin was built with Mac in mind, I'm afraid.


----------



## johndowd

Any other options you can think of that would allow me to post examples?   Thanks for getting back to me!


----------



## Joe4

> Any other options you can think of that would allow me to post examples? Thanks for getting back to me!


See if any of the options discussed here work for you: http://www.mrexcel.com/forum/showthread.php?t=508133


----------



## Drewgarry007

I have just installed this and when I paste it into a post I get this:Excel WorkbookABC3kklkmjlkj4980#l'lkkljSheet1*Excel 2002*

what am I doing wrong?


----------



## Drewgarry007

Does anyone know what I am doing wrong here?


----------



## 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!


----------



## RoryA

Perhaps you are pasting in a forum that has HTML turned off (like this one) - you can check in the bottom left corner of the page. I pasted that into a new post in the Test forum here and as you can see it works.


----------



## jaslake

Does Html maker work on this Forum? Pasted this from an Excel 2007 worksheet...this is the output.


Excel 2007B9ramSheet2


----------



## jaslake

How do I do this...I don't see the Option to allow HTML...



> *How do I use this add-in?
> *Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
> 
> 
> Make sure you have enabled HTML in your board profile.
> Click the "profile" link--which is in the group of text links at the top-right of every page of the board.
> In the list of preferences, make sure the option for "Always allow HTML" is set to *Yes*.
> Click the "Submit" button to save any changes to your profile


----------



## RoryA

As I mentioned earlier in the thread, HTML is off in this part of the forum (you can check in the bottom right corner of the page)


----------



## jaslake

Hi RoryA

What does this mean





> HTML is off in this part of the forum


If I wish to post a snapshot of Code Results in the Excel Questions Forum...how do I do that. I see other Members do so...am I able to or not? If not, why not...what are the Rules?


----------



## jaslake

*Re: HTML maker - testers required!*

Hi RoryA

Never mind...I sorted it out...

This is a bit misleading as I see no reference to "preferences" in the Profile Link. It appears HTML must be initiated by wrapping HTML Tags...much the same as Code Tags





> *How do I use this add-in?
> *Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
> 
> 
> 
> Make sure you have enabled HTML in your board profile.
> 
> Click the "profile" link--which is in the group of text links at the top-right of every page of the board.
> In the list of preferences, make sure the option for "Always allow HTML" is set to *Yes*.
> Click the "Submit" button to save any changes to your profile


----------



## RoryA

*Re: HTML maker - testers required!*

It does appear the instructions are out of date - I'll see about changing them.


----------



## DocAElstein

*1 Message Box.  2 Bold Lines*



schielrn said:


> One thing for me that might be nice and not sure what others think, is when you click the Generate html code, is to have a message box or something come up saying that it is copied to the clipboard. But I guess after using it and understanding what happens, this is not necessary.
> 
> Only reason I say this, is because I clicked the dang on button like 10 times thinking it was doing nothing and then thought to myself I will try to paste it and voila what do you know it was already copied to the clipboard.


.1      I think as well that would be good (but not essential) idea. I started using the VB HTML Maker (the one that copies code and keeps all color formats etc.) first which has such a box. So then when I started using the HTML I hit my head against a brick wall clicking thinking it had not worked when indeed it had already copied to the clipboard. 
.2      By the way the HTML maker I find great and got it to work quite easily despite being a computer novice (Late Starter). One small point:- It does not seem to copy cell boarders (thick, bold lines and the such). So I could not for example use this method to highlight things or particular cells. But the background color is copied so that gives an alternative way to highlight cells.


----------



## lmmay

Is this HTML editor still available?


----------



## hiker95

lmmay,



> Is this HTML editor still available?



Yes.


*1.* MrExcel  HTMLMaker20101230

*https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189*

Installation instructions here:

*http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970*


----------



## 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!


----------



## gosselien

Hello,
sorry for my poor english...

i install HTML MAKER (XL 2007 Win XP) and in sheet, i don't see anything new ...
Where is the position of this add in on the sheet screen please ?  Not with my Code Documentor 


maybe, i make a mistake in install ?

Mny Thanks 

Patrick


----------



## Smitty

You should see a new tab on the Ribbon called Add-Ins.  You'll find a menu for the MrExcel HTML Maker there.

HTH,


----------



## DocAElstein

gosselien said:


> Hello,
> sorry for my poor english...
> 
> i install HTML MAKER (XL 2007 Win XP) and in sheet, i don't see anything new ...
> Where is the position of this add in on the sheet screen please ?  Not with my Code Documentor
> 
> 
> maybe, i make a mistake in install ?
> 
> Mny Thanks
> 
> Patrick



Hi
.  You should also see extra options shown when you click on the right mouse....
.  Read also this Thread...... 
http://www.mrexcel.com/forum/excel-questions/837991-excel-forum-software-4.html?

Alan
Germany

P.s. I use mostly XL 2007 (German)  and XP. I use the HTML Maker extensively.--Works great!


----------



## RoryA

And if you download the add-in from the link in my signature, you'll get a proper new ribbon tab (it's the same HTML Maker code).


----------



## DocAElstein

RoryA said:


> And if you download the add-in from the link in my signature, you'll get a proper new ribbon tab (it's the same HTML Maker code).



Hi Rory…

.  I just downloaded that new one  and (Think?) I installed it. But I already had the old  one?  So I am not sure..  The Ribbon looks the same as it always did….






.. But I think looking at the development window I have the new one





.   Questions:.
. 1)  It looks like I have the new one, Correct?

. 2)  I am only using Windows (XP). So for me there are no changes, Correct?

. 3)  It did error just once at the start as shown in Yellow. I guess that was just as the Ribbon Tab was already was there. So I do not need to worry about it. Correct.

. 4)  Your Tools do not have the extra right Mouse click stuff . Correct? (I realize you do not need it as everything is up in the ribbon to be found..)

.  Thanks for making your Tools Public
.  They are very useful, fun to play with. (Stupidly I only just realized that they have almost everything the MrExcel HTML Maker has and a few thing more. ! .. So really using your Tools seem probably the best option. )

. Alan

.  P.s . Just in case you ever do any updates… A small extra bit would be handy…like the bit in the shg add-in I uploaded today from that Thread, Post #3
http://www.mrexcel.com/forum/excel-questions/837991-excel-forum-software.html?
.. This extra bit is  handy when you use the Spreadsheet to make a simple Table to post in a Thread.


----------



## gosselien

Hello,

this link to onedrive don't work here ...but i download anyway elsewhere

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970



this is a copy of my excel screen 

https://www.dropbox.com/s/dd9qi97367o63gt/Copie_Ecran027.jpg?dl=0

and my compléments:

https://www.dropbox.com/s/y11lfjbv6yg7ug3/Copie_Ecran028.jpg?dl=0


----------



## gosselien

Hello,

it's ok now...with my Firefox and not Chrome 


i don't have the good html Maker before !!!

I try this and go back after


----------



## DocAElstein

gosselien said:


> ......
> 
> I try this and go back after



?  It is very difficult to understand your English
.  Have you read  #223 amd # 224 in this Thread and followed the links there??


? Il est très difficile de comprendre votre anglais 
. Avez-vous lu # 223 # 224 amd dans ce fil et il a suivi la gauche ?



Alan


----------



## gosselien

ok...

i try to download first with Chrome and nuts... after , i try to download with Firefox and now it's ok...

i make a text here 

http://www.mrexcel.com/forum/test-here/838384-test-pmk.html#post4085401

Thanks 

Patrick


----------



## gosselien

re Hello

there is no version in French ? 

Thanks


----------



## 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!


----------



## RoryA

DocAElstein said:


> P.s . Just in case you ever do any updates… A small extra bit would be handy…like the bit in the shg add-in I uploaded today from that Thread, Post #3
> http://www.mrexcel.com/forum/excel-questions/837991-excel-forum-software.html?
> .. This extra bit is  handy when you use the Spreadsheet to make a simple Table to post in a Thread.



The BBCode bit of mine is the same as shg's add-in.

You seem to have downloaded the Mac version for some reason?


----------



## DocAElstein

Hi Rory, thanks for the reply.



RoryA said:


> You seem to have downloaded the Mac version for some reason?


 
… Because I did not read the notes properly.!! Oops!!  I Thought it was an update. I stupidly Just did not notice that the mac version was a separate extra.
.  So I deleted everything, started again and now I have everything correctly! Brilliant. Thanks



RoryA said:


> The BBCode bit of mine is the same as shg's add-in.


 
….. I think the extra bit is still not there…
Yours…







……………………………….
Shg from Post #3 
http://www.mrexcel.com/forum/excel-questions/837991-excel-forum-software.html
https://app.box.com/s/soezox25h3w0q5s4rcyl









But no problem I can use shg for a table without headers… (I just have to have it monochrome as it errors at a line which I think makes the colors..



		Code:
__


        [color=blue]End[/color] [color=blue]Select[/color]
 
        [color=blue]If[/color] .Font.Bold [color=blue]Then[/color] AddTag sL, sR, "B"
        [color=blue]If[/color] .Font.Italic [color=blue]Then[/color] AddTag sL, sR, "I"
        [color=lightgreen]'If .DisplayFormat.Font.Color <> vbBlack Then AddTag sL, sR, "COLOR", sBbRGB(.DisplayFormat.Font.Color)'Error 428 : Object  does not support this Property or Method[/color]
        [color=blue]If[/color] .Font.Underline <> xlUnderlineStyleNone [color=blue]Then[/color] AddTag sL, sR, "U"
 
        [color=blue]If[/color] bFrm [color=blue]Then[/color]

 
.. so I have to edit that bit out. May just be my system? (XL 2007 )

So got there in the end again
Thanks.
Alan


----------



## gosselien

RoryA said:


> The BBCode bit of mine is the same as shg's add-in.
> 
> You seem to have downloaded the Mac version for some reason?



Hello,

is it possible to download the addin BBcode and where ?
What are the possibility with this add in ? 

Patrick (XP and xl 2007)


----------



## RoryA

DocAElstein said:


> ….. I think the extra bit is still not there…



OK, I have updated the add-in on my site.


----------



## DocAElstein

RoryA said:


> OK, I have updated the add-in on my site.




Hi

Brilliant, thanks..
When you do it could you check out the error bit with the color? (Or don’t bother – I will let you know!)
(… shg does not like colors… he may have dropped in that error on purpose!  - only joking!! )

Alan

………………………………………………….

P.s.  Just some minor long term suggestions, if it were possible.

. 1) If your BBCode with the Formula option could produce the color breakdown like the HTML maker does.. that  would be a crowning opting I think.

HTML Maker:





Yours With Formulas:


P​4​{=INDEX(INDEX($A$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15)))),3),)}​


… but not a big deal.. I am in a small minority with my color obsession!!


P.s. 2

.  For me it always looks better if the table size is reduced (to smallest – not sure if that is size 1? ) because there are often problems with the width when trying to post a Table with lots of columns. I think lots of people have problems with that


P​4​{=INDEX(INDEX($A$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15)))),3),)}​


P.s. 3

.  And a very last unimportant one: It would be helpful if after copying successfully to the clipboard, a Message box pops up to confirm that it “works” . This feature is in the Mr Excel Visual Basic Code  Maker.






………








….And the absence of it in the HTML Screenshot maker often catches people out, not realizing that it has been copied.

………………………………..


----------



## gosselien

Hello,

i m testing "Copy range to Clipboard" and i have a error here: (line 190)

140                       Case vbEmpty
                              ' nada
150                   End Select
160           End Select


170           If .Font.Bold Then AddTag sL, sR, "B"
180           If .Font.Italic Then AddTag sL, sR, "I"
190           If .DisplayFormat.Font.Color <> vbBlack Then AddTag sL, sR, "COLOR", sBbRGB(.DisplayFormat.Font.Color)
200           If .Font.Underline <> xlUnderlineStyleNone Then AddTag sL, sR, "U"


210           If bFrm Then
220               If .HasFormula Then


----------



## DocAElstein

gosselien said:


> Hello,
> 
> is it possible to download the addin BBcode and where ?
> 
> 
> Patrick (XP and xl 2007)


 

Ask Mr. Excel - VBA HTML Maker

………………………………………….




gosselien said:


> Hello,
> 
> 
> What are the possibility with this add in ?
> 
> Patrick (XP and xl 2007)


 












		Code:
__


[color=blue]Public[/color] [color=blue]Function[/color] GetURLRoaryA(cell [color=blue]As[/color] Range, [color=blue]Optional[/color] default_value [color=blue]As[/color] [color=blue]Variant[/color]) [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'When an Array is assigned through Let to this function, it returns an Array which then through effectively A "Let One Liner" becomes a modified Range based on the Supplied range and any Additional Arguments. It can be thought as a normal Function working on a input Range. A Particular characteristic here is that the Output is created in a loop which specifically assigns each cell within the range. This probably ensures that VBA in any further workings "Know" or "allows" for an Array and so for example ensures that this Function can be used in Evaluate Function "One liners" without the usual "coercing stuff". I think variant is the only type of Function capable of returning an Array. By val use the value held in the value within the sub (Function) , rather than referencing the allocated source "bucket" of the variable. So any given value to the Variable outside the Sub (Function) are not changed[/color]
[color=lightgreen]'     'Lists the Hyperlink Addresses for a given range[/color]
[color=lightgreen]'     'If cell does not contain a hyperlink, return default_value[/color]
[color=lightgreen]'                                    Dim rCell As Range'...." started out with a simpler version and then decided to redo it to handle ranges with more than one row/column but forgot to remove the variable declaration."[/color]
    [color=blue]Dim[/color] vOut() [color=blue]As[/color] [color=blue]Variant[/color]
    [color=blue]Dim[/color] x [color=blue]As[/color] Long: [color=blue]Dim[/color] y [color=blue]As[/color] Long [color=lightgreen]'We build a collection of output by looping into an Array. So these variables will be used for both Row,Column indicies for the cell Range coming into the Function, as well as the Array Indicies. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
    [color=blue]If[/color] IsMissing(default_value) [color=blue]Then[/color] default_value = vbNullString [color=lightgreen]'I guess this is important to give it an empty cell rather than not yet anythng, as not yet anything may cause strange errors somewhere[/color]


----------



## DocAElstein

gosselien said:


> Hello,
> 
> i m testing "Copy range to Clipboard" and i have a error here: (line 190)
> 
> .............
> 
> .........




see my Post # 232 in this Thread....

and Post # 6 here:
http://www.mrexcel.com/forum/test-here/838395-forumtools.html?#post4085623

......



RoryA said:


> ...........
> 
> The DisplayFormat property wasn't available pre Excel 2010 so I'll add a check for that.


----------



## DocAElstein

RoryA said:


> OK, I have updated the add-in on my site.



......and (very) shortly after.......



RoryA said:


> OK, I've uploaded a new version of the PC add-in which now has options for Grid, Headers and coloured formula parts. Oh, and a message on successful copy as well as a Cancel button on the form.





Row\ColP​5​=INDEX(INDEX($A$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15)))),3),)​
 

…. Words fail me… Amazing!  We and the Forum Software are not worthy! 


<a href="http://s1065.photobucket.com/user/DocAElstein/media/MegaRoryTool_zpsgpm7ll6u.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/MegaRoryTool_zpsgpm7ll6u.jpg" border="0" alt=" photo MegaRoryTool_zpsgpm7ll6u.jpg"/></a>


----------

