Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retain Comments in HTML? (Excel 2000)

    I'd like to publish a spreadsheet to the web but prevent the Comments from getting stripped off. Any ideas?

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    I've wondered about preserving comments on more than one occassion myself. The idea (just an idea) I had was to "publish" a new sheet to the workbook, with the comments appearing as actual cell text, and then publishing/ saving that as HTML. I haven't looked at the VBA to do this, but I'm sure it's not too hard. As to the actual web page generation, I've been having a look at a few Windows Scripting Host scripts, that show some promise in selectively extracting spreadsheet data and generating the appropriate HTML. I could try to dig these out again if you're interested.

    Alan

  3. #3
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Hey, thanks for responding. What I'd like to do is publish a spreadsheet to my website that has data in the cells but also retains the Comments. I'm hoping they would act like footnotes to explain the values or formulas to a client, for example. To me, this seems like a very natural use of this feature, but I guessing there's some security issue? I notice that Word's Comments survive the trip to HTML, by the way. I'm pretty new to VBA, but if you've got an idea, I'm willing to try it. And I've never heard of a Windows Scripting Host script, although I get the feeling I need that to get the VBA stuff into HTML. I really appreciate your help.

    Jim

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Hi Jim

    Like I said, I've only had thoughts on these matters, not actually got round to the "doing" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. By "retaining the comments" I presume you'd like them to respond to a "mouseover" event and pop up in boxes, like in the Word doc you mention. I'm not sure of how to organise this in HTML, unless the cell contents appear as links. But maybe there is a way. There will certainly be a way to extract them for some sort of display.

    I'm unsure about what security issues you're thinking of here.

    The VBA and WSH just represent different ways to achieve the end result. Once you have your requirements well defined, I'm sure you'll get all the help you need here, especially if you choose the VBA route. Meantime, there are a couple of links that come to mind to whet your appetite:

    http://www.interq.or.jp/sun/puremis/colo/HTMLMaker.htm
    HtmlMaker2.32

    http://www.wshscripting.com/authors/1/submissions/ ...
    http://www.wshscripting.com/view/1146.html
    Reading Excel Data From WSH [UTIL]
    A demonstration of using WSH to read data from an Excel file.

    I'd be interested in following this through with you Jim. It may be relevant to a fairly significant project I'm involved with.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Jim

    Here's something I found (http://www.ozgrid.com/VBA/ExtractCommentText.htm) that might be of use to you. It's a user defined function (UDF) that you can add to your workbook. The VBA is already done for you, except for the inclusion in the project, which the instructions provide for...

    This UDF will extract the text from a cell comment

    The Code


    Function GetCommentText(rCommentCell As Range)
    Dim strGotIt As String
    On Error Resume Next
    strGotIt = WorksheetFunction.Clean _
    (rCommentCell.Comment.Text)
    GetCommentText = strGotIt
    On Error GoTo 0
    End Function



    To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

    =GetCommentText(A1)
    Where cell A1 has a cell comment. (Insert>Comment)

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Jim:

    I have little experience exporting sheets to HTML in the manner you mention. But since your main concern is:
    3) On the web, allow my original Comments to

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Incidentally, the tag is used like this:
    <span title="Cell comment text here">Actual cell text here</span>

    Alan

  8. #8
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    <P ID="edit" class=small>(Edited by WebGenii on 20-Nov-02 12:06. Edited to add hyperlink)</P>Alan:

    Thanks a lot for your help on this. I haven

  9. #9
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Alan:

    In the last couple of days, I've learned a little. I've tried HtmlMaker, XLS->HTML Easy Converter, PGMagellan and Excel's own Publish As Web Page feature. They all convert my spreadsheet into HTML, each in its own way, it seems. Yes, that <span> tag works nicely. You don't even need it in Excel, because it publishes the Comments itself. But with each of these approaches, there is one signicant drawback: no interactivity.

    My primary goal is to publish a speadsheet that the user can toy with on the web, and then even download to customize even further. The only way to retain that interactivity seems to be Excel's Publish As Web Page With Interactivity feature. It gives you a very nice looking spreadsheet on the web, and you can manipulate values and formulas just like in Excel. It's definitely cool.

    But cool seems to have its price. Using this method, I lose my Comments. What is more, Excel puts out a whole lot of code that seems pretty locked up--I can't figure out where to <span>. And even if I could, I wonder how that would affect the sheet if the user downloads it.

    So, it seems I can have interactivity, or I can have Comments. But not both.

    Unless somebody has a bright idea, I'm about ready to throw in the towel on this. But, again, I really appreciate your help.

    Jim

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Jim:

    I've learned quite a bit myself, playing around with the possibilities. As I said, I had little familiarity with the publishing of an interactive sheet to a web page. I tend to agree that this "Excel feature" and the option of exporting to "pure HTML" tables are quite different approaches that can't be integrated into a single "best of both worlds" web page. Once I saw the type of exported sheet you spoke of, I realised that such a degree of interactivity would not be possible with an export to an HTML table.

    The only thing I could think of was to go for the HTML table approach, with the comments etc. the way you'd like them, and a link to open the interactive sheet in another browser window. The user would have to toggle between the two windows, but this might have the added advantage of a static original sheet to refer back to after messing up the editable one <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>.

    I can't follow what you said:
    <hr>Yes, that <span> tag works nicely. You don't even need it in Excel, because it publishes the Comments itself. <hr>
    I couldn't get comments out at all unless I got them into cell text first.

    Still worthwhile pursuing IMHO. Let's know what, if any, method you decide upon. I'm still very interested in participating if appropriate.

    cheers

    Alan

  11. #11
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Alan:

    Sorry that I wasn't clear about that sentence. What I was trying to say is: I found your "span" tag very helpful with the output from HTMLMaker and other utilities. Their output, of course, was non-interactive, but I could edit the HTML to display beautiful litttle pop-up messages.

    But here's something interesting: in Excel, you actually can get the spreadsheet to preserve the Comments (if you don't mind sacrificing interactivity).

    To see what I mean, make a small spreadsheet and add some Comments to the entries in some of the cells. When you Save As Web Page, you deselect "Add Interactivity". When I do this, I get a page that shows the spreadsheet with a (1) after the first Comment, a (2) after the second, and so on. If you hover the pointer over that number, your Comment pops up.

    The (n)'s formatting seems to be controlled by some style definition in the HTML--when I tried this with different files, it sometimes came up as normal-size text colored blue, and then for a while it was coming up in red superscript, much like a footnote.

    As promising as this was, I finally decided that interactivity was more important than Comments. I like the familiar look of the Excel output, and the idea that the user can play what-if right on the web site, then, if he wants, he can download it very simply as his own Excel file (although he can't permanently change the original file on the site).

    I agree that the best solution currently seems to be two files with hyperlinks. But, for simplicity, I think I ought to stick with one worksheet, not two. One file would be the worksheet with color-coded cells for the user to change. It would also have hyperlinks to another worksheet of Help and explanation copy.

    If I can figure it out, it would be cool to have the two windows on screen at the same time. Hey, it's not Comments, but close. What do you think?

    Jim

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Here's a late entry I just came across. Haven't tried it myself, so don't know its capabilities wrt comments.
    http://businesscalculators.com

    Alan

  13. #13
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    <P ID="edit" class=small>(Edited by WebGenii on 01-Jan-03 23:19. Edited to add hyperlink)</P>Thanks for the idea. I played around a little with it, but I didn't see much potential for comments and interactivity so far. As I mentioned before, I've settled on the idea of publishing the page from Excel. That way, it has all the cool formating from Excel, retains interactivity on the web, is easily updatable and can easily be downloaded. I don't have the Comments in there, but I've linked to a help file. If you'd like to see it, check http://http:/www.mcdrtv.com/Calculatorpage. Thanks again for all your help!

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Looks great! Had a little play and downloaded the spreadsheet for a closer look <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33> .
    BTW, the edited edited URL is: http://www.mcdrtv.com/CalculatorPage.htm (case sensitive).
    I presume you mean http://www.mcdrtv.com/Financial Facts.htm when you mention the "help file".

    nice job!

    Alan

  15. #15
    Lounger
    Join Date
    Nov 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retain Comments in HTML? (Excel 2000)

    Thanks, I really appreciate your help!

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •