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