Load a Spreadsheet from a DB2/400 Database: Part 2

Published: July 11, 2007

by Michael Sansoterra

Note: The code for this article is available for download here

Ted Holt's tip on loading database data into Excel was great. I'd like to offer a few potential enhancements to the VBA code that was offered in the tip.

The first thing that I would like to note is that if you want to copy all rows and columns from an ADO recordset (an object that holds the results of a query) into the spreadsheet, then there is a CopyFromRecordset method of the Range object to do this:

ws.Range("A2").CopyFromRecordset Rs

In the original code sample, row one was reserved for headings. In this example, the contents (rows and columns) of the recordset object will be dumped into the spreadsheet starting from cell A2. This method is much faster than iterating through the recordset programmatically. Of course there may be some circumstances when a programmatic step through the recordset is warranted, in which case dumping the whole recordset onto a worksheet would not be appropriate. Also, don't forget that some of the "exotic" database data types such as large objects and DataLinks may not be compatible with this command. Further, if you're using CHAR or VARCHAR data tagged with CCSID 65535, CopyFromRecordset will fail because it doesn't know what to do with binary data. This character data should be CAST to a different CCSID in the query, or the option to translate CCSID 65535 automatically should be activated for the IBM ODBC driver or OLE DB provider.

A second potential improvement is to save the user grief by formatting the spreadsheet columns automatically based on the underlying database column's data type. Fortunately, an ADO recordset contains a field object for every column in the recordset. Among other properties, the field object has a type property that indicates its database data type (CHAR, INTEGER, DECIMAL, etc.) If we know that a recordset field is a text field (SQL Types CHAR or VARCHAR, for example) we can automatically format the column as text instead of letting Excel guess the data type. Have you ever downloaded a CHAR(5) zip code column into Excel only to have a leading zero truncated because Excel kindly (but wrongly) interpreted the data as numeric? Formatting the column appropriately will fix this problem. For another example, if we know the data type is currency, then we can automatically format the column accordingly using a currency symbol and a thousands separator.

The original program contained a loop to iterate through each column in the recordset and place the column name as a heading in the spreadsheet. Adding a simple VBA "If" statement to this loop to assign a spreadsheet format to the current column is all it takes:

    If lngType = adDate _
    Or lngType = adDBDate Then
        strFormat = "yyyy-mm-dd"
    ElseIf lngType = adDBTime Then
        strFormat = "hh:mm:ss"

The if structure needs to account for each possible data type. You may want to review and enhance this section of code to meet your particular needs as the generic formats I picked may not be quite right for your application. Once the format is selected, simply assign it to the current column in the worksheet's column collection (where variable col refers to a column letter in the spreadsheet):

For my third comment, a few other simple things can be done to enhance the code. These are:

• Activating the hour glass so the user knows that processing is occurring:
Application.Cursor = xlWait. Revert it to normal when processing is done: Application.Cursor = xlDefault
• "Auto-fitting" the rows and columns to a "best fit" for the row heights and column widths once the data has been loaded:
  Cells.Select
  Cells.EntireColumn.AutoFit
  Cells.EntireRow.AutoFit
• Finally, we can make the headings stand out a little by activating the bold property for Row 1 of the spreadsheet:ws.Rows(1).Font.Bold = True

Click here for the revised sample code. Please note that my subroutine name is different than the Workbook_Open subroutine name used in the original code. It was tested with Excel 2000 and Excel 2007. The column formatting portion of code may need a little tinkering to work with 2002 and 2003 as each version of Excel seems to be a little quirky with its string formats. Enjoy!

RELATED STORY

Load a Spreadsheet from a DB2/400 Database

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.