Very often, my “customers” asked me to retrieve records from database and pass it to them as an Excel Document. Previously, I need to manually set the width and wrap each of the columns.

I found it tedious and time wasting (the manual formatting, not the user’s request :D)

This macro below will automatically set all columns to auto-fit and wrap those columns that are too wide.

To add macro to your Excel, follow these steps:

1. Click Tools -> Macro -> Record New Macro
2. Give the Macro a meaningful name and description. The shortcut key is optional. If you want to make the Macro available in any Excel in your PC, store the macro in “Personal Macro Workbook”. Click OK to continue.
   
3. A small floating window will appear. Click the ‘Stop Recording’ button.
4. Click Tools -> Macro -> Visual Basic Editor (or press Alt-F11)
5. Complete the code as shown below
6. Save it. The macro now will be run whenever you press the shortcut key (if you defined it), or you could find the macro through clicking Tools -> Macro -> Macros (or by pressing Alt-F8)

Sub FormatQueryResult()
'
' FormatQueryResult Macro
' To make the Database query result less irritating to the eyes
'
    Dim i As Integer 'counter
    SendKeys "^a", True 'Send Ctrl-A to select All
        
    'Make the column auto fit
    For i = 1 To Selection.Columns.Count
        ActiveSheet.Columns(i).AutoFit
    Next
    
    'Wrap columns that are too wide
    For i = 1 To Selection.Columns.Count
       If ActiveSheet.Columns(i).Width >= 250 Then
            ActiveSheet.Columns(i).ColumnWidth = 65
            ActiveSheet.Columns(i).WrapText = True
        End If
    Next
End Sub

Upon its release, I immediately downloaded and installed Google Chrome. After trying for a while, I found out that Java Applet is not yet supported. It was a big NO-NO since I frequently use DBS’ Internet Banking, which uses secure Java applet.

So today I give Chrome another try (for obvious reason, system reinstall). Apparently now Java plugin is supported by Google Chrome. You could download the JRE (Java Runtime Environment) HERE.

So I installed the beta JRE, restarted Chrome, then curiously visited DBS’ Internet banking site. When the page loaded, I noticed that the JRE icon is now loaded in Windows’ System Tray. But it seems the plugin doesn’t work as shown below

Then I used that old trick, something similar to banging your TV when the picture got blurry. I refreshed the page by pressing F5. Click continue, then Voila! It the login page is now displaying properly.

UPDATE (15 Oct 2008):
I can’t use the transfer functionality. The session was abruptly ended.

Couple of weeks back, my colleague mentioned about Selenium. It is a tool to automate UI testing which utilizes heavily on Java script. I gave it a short try but stumbled with problem on retrieving values from table (I had not read the documentation that time). So I stopped and was pondering on how to resolve the problem. Due to busy with other projects, I didn’t continue my exploration with Selenium.

When I attended Microsoft’s Mix-It-Up Session, one of the speakers highlighted the capability of Visual Studio 2008 to generate automatic UI testing. It immediately reminded me of Selenium. So I asked a question about the problem similar to what I had with Selenium. I asked, “Is there anyway to make the UI Testing dynamic so that we can supply list of input values and the expected output values, then it will automatically generate all the asserts?”. The speaker did not answer my question, but he feedback to Microsoft Team that what I asked is something that would be very nice to have in Visual Studio 2008.

I haven’t get my hands dirty with Visual Studio 2008, so I can’t really confirm with what the speaker implied in his feedback to Microsoft *grin*.

Anyway, last weekend I have quite of free time. So I decided to look deeper into Selenium. This time I thread through carefully the Selenium documentation. I finally found what I was looking for. The function is called “storeTable (TableCellAddress, VariableName)”.

To generate the test script, I used Selenium Firefox Add-On. Then I modified manually the script to suit my needs using Notepad++. From here, it’s quite smooth sailing with Selenium.

Few things that I learned:

When open a pop-up window, you need to have the windowID. WindowID is the variable that holding the reference to that window.

var myWindowID = window.open("http://window.url");

After you select the window using SelectWindow, you need to call waitForPageToLoad before accessing any elements inside it. Fail to do this; your test case might throw exception.

As you could see, I am quite late into this. There are myriads of articles (sample) and blog posts (sample) about Selenium available in internet. Google is your best friend 😀