VBA To HTML

Posting VBA code to a website can be a real pain. When I started this blog, I thought of writing a program to convert procedures into HTML so that I could easily post them. It didn’t take much research to find that this had already been done.

I’ve been using zHTML until a couple of days ago. It’s a nice product and has worked reasonably well. I particularly like the interface – one menu item and a subsequent userform for options as opposed to a bunch of menu items. The program isn’t perfect though. For one, it doesn’t close the first two font tags. If you’re a regular reader here you may have seen that the formatting of the entire blog gets screwy – at least until I read my own blog and fix it. So I have to close the font tags manually, and I often forget.

The HTML produced by zHTML is well formatted and easy to read. That, as it turns out, is a problem for me. TypePad, my current blog host, has an option to convert line breaks. When I hit enter, it creates a line break, which is nice. However, the line breaks in the HTML get doubled. I could turn off the option in TypePad, but then I’d have to insert line breaks manually for everything but the code samples. For now, I’ve been deleting the br tags from zHTML, which is a pain in the butt.

Recently, I started browsing around the Mr. Excel forums. Juan Pablo Gonzalez, who I met at this year’s summit, is an MVP over there and I wanted to see what is was like. Juan wrote an Add-in to convert VBA to HTML and BBcode (whatever the hell that is) called VBA HTML Maker. It’s also a very nice program. The code is unprotected, but I haven’t had the time to look through it thoroughly yet. I have a few problems with VBA HTML Maker, but I’ll just list out what I want in a converter.

  • One entry point to the code. I don’t like that Maker puts all those buttons on my menu and right click popup menu. Give me one “convert” menu item and use a form to let me choose the rest. Or even a nested menu would be Okay.
  • Font – I want to choose the font face and size. I would choose Courier New and 2 which is what zHTML uses, but Maker doesn’t set the size. Give me an options form where I can make those choices once and they apply to every conversion I do.
  • Line breaks. I may be alone on this one, but on the options form, I want to decide whether to format the HTML output. Maker does it the way I want for this blog, but that may not be what everyone wants. Give me the choice of output – formatted like zHTML or unformatted like Maker.
  • Output location. zHTML creates an htm file in the root directory. It doesn’t tell you the name, but after a couple of times, it’s pretty easy to find. Maker copies the HTML code to the clipboard. Copying to the clipboard is what works for me, but how about making it an option. Let me choose whether to copy to the clipboard or create a file. Then let me choose which directory and file name (with appropriate defaults if I don’t choose).
  • Keyword list. Neither of these programs properly colors Debug.Print and Debug.Assert. zHTML includes a text file of its list of keywords so you can add keywords that you want. The keywords in Maker are in a sheet, so it can be edited by changing the workbook from an add-in. Being able to edit the list is key.

In the grand tradition of programming, I complain, but don’t actually do anything about it. I do hope to get through the Maker code to see how it works. I’ll see if time permits.

4 thoughts on “VBA To HTML

  1. Kirankumar

    Sir
    how to automate web sites using vba code?is it possible?

    Example : entering username and password ,clicking hyperlink,entering values in check box
    through VBA code

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax