Resources‎ > ‎Tips and Tricks‎ > ‎Excel‎ > ‎VBA‎ > ‎

Example of querying from a database in VBA

This is a simple example of how to pull data from a datasource.

Prerequisites

First, from within the VBA editor, ensure that you have all the needed references.  Under Tools->References, ensure that the Microsoft ActiveX Data Objects 2.6 or higher is selected.  

Example

public sub myTest()

   Dim cn As New ADODB.Connection
   Dim rst As New ADODB.Recordset

   On Error GoTo handleError 

   ' This will ensure that the user will be prompted for a username/password
   cn.Properties("Prompt") = 2
   cn.Open "dsn=myODBCDSN;Pwd=;UID=;"

   rst.ActiveConnection = cn
   rst.Open "Select * from " & schemaName & "." & tablename

   mRow = 0
   If numRows > 0 Then
      rst.MoveFirst
      Do Until rst.EOF
         mRow = mRow + 1
         For mCol = 1 To rst.Fields.Count
            ' Copy the field names (ie, headers) if it is the first row
            If (mRow = 1) Then
               ActiveSheet.Cells(mRow + 3, mCol ).Value = rst.Fields(mCol - 1).Name
               If (rst.Fields(mCol - 1).Type = 135) Then
                  ' Date column
                   
ActiveSheet.Columns(mCol).NumberFormat = "dd-mmm-yyyy"
               End If
            End If
            If Not IsNull(rst.Fields(mCol - 1)) Then
               tmpString = rst.Fields(mCol - 1)
               If (Left(tmpString, 1) = "=") Then
                  tmpString = "'" + tmpString
               End If
                
ActiveSheet.Cells(mRow + 4, i).Value = tmpString
            End If
         Next mCol
         rst.MoveNext
      Loop
   End If

   rst.Close
   
ActiveSheet.UsedRange.Columns.AutoFit
   ActiveSheet.UsedRange.Rows.AutoFit

handleError:
   If (Err.Number > 0) Then
      ErrMsg = Error(Err)
      MsgBox ("Error: " & ErrMsg)
   End If
   cn.Close
   Set cn = Nothing

End Sub

Comments