StarQuest Technical Documents

Using Visual Basic for Applications (VBA) and ADO with StarSQL

Last Update: 06 January 2012
Product: StarSQL
Version: v5.x
Article ID: SQV00SQ056

Abstract

This document describes how to use the StarSQL software, Microsoft Visual Basic for Applications (VBA), and ADO to automatically populate an Excel spreadsheet with DB2 data. Similar VBA applications can be used with the other members of the Microsoft Office Suite such as Word and Access, as well as other applications that support VBA.

The Visual Basic for Applications programming language is similar, but not identical, to Visual Basic. There are several StarQuest tech notes related to using StarSQL with Visual Basic that may be used to provide additional examples.

The example below has been tested with Excel from Office 2007 and Office 2010.

Solution

1. Start Excel and create a new workbook.
2. Type Alt-F11 to enter the VBA development environment.
3. From the Tools menu, select References. Scroll down and select the checkbox for Microsoft ActiveX Data Objects 2.8 Library.
4. From the Run menu, select Run Macro. This will bring up a dialog that allows you to view, create and edit macros.
   
5. Enter Auto_Open as the Macro Name. Be sure you are creating the macro in VBAProject(book) rather than <All Projects>.
6. Click the Create button.
   
7. In the code dialog, enter the following code:

Sub Auto_Open()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "MYDSN", "myuser", "mypassword"

rst.ActiveConnection = cnn

rst.Source = "SELECT * FROM MYTABLE"
rst.Open

' Clear the existing data from the spreadsheet
ActiveSheet.Cells.Clear

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(2, 1).CopyFromRecordset rst

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub

8. To test the newly-created macro, select Run Sub/User Form from the Run menu. This should populate the workbook with the contents of the DB2 table.
9. Save the Excel document as a macro-enabled workbook (type .xlsm)
10. Exit Excel.
11. Re-open the Excel document. Depending on the Excel macro security settings, you may see a Message Bar:
    Security Warning: Macros have been disabled.
    
12. Click the Enable Content button. The contents of the spreadsheet will be re-populated with the current contents of the DB2 table. Excel 2010 will remember your choice to Enable Content when you re-open the spreadsheet in the future.