Thursday, October 05, 2006

Help converting Excel tables into html

One of the most frustrating experiences in personal finance blogging is finding ways to convert excel spreadsheets to html.

Let's face it. PF bloggers like numbers. They crunch them, they calculate them, they tabulate them and they like to display them!!

It's incredibly frustrating writing a PF blog without the ability to insert excel spreadsheets and tables. Virtually all of us use one of two techniques:

  1. Take a screen shot and save the picture as a gif file (use ctrl-print screen to save the current window to the clipboard. load the picture into a graphics program and save as .gif or .jpg).
  2. Hand code html tables - I'm not ready to invest that much into a technical skill or the time to make the table once the skill is learned - but many do.

Almost in vain, I've subscribed to googel's news groups looking for posts discussing the conversion of excel tables to html. For almost 7 months I've had to delete almost every e-mail sent as useless, until today.

Check out Excel to HTML conversions!

I warn you, the process is not simple, but it isn't too hard either (I did it!).

Excel to HTML conversions sends you to a web page describing how to use excel VBA macros to convert part of an excel spreadsheet into a lean html document.

The key is to follow the link to the excel VBA module. Copy the module into a spreadsheet and run the excel2html macro from the tools->Macro menu. Thorough instructions how to do this can be found here. After successfully saving the Macro, you should save the spreadsheet to allow you to convert future tables. If you would like to have ongoing access to the macro from any spreadsheet, you should save the Macro in the personal.xls spreadsheet - which stores macros and functions permamently available.

For those not already kings of VBA, it will probably take 20-30 minutes to read and experiment with the functions. So far the macro pulls up very nice and small html (compared to excel's save as html function).

I haven't tried copying and pasting the output code into blogger's Edit HTML tab, but I will next week when I have a chance to experiment with it.

Please post your experiences with the macro.

Have a wondreful day,


No comments: