Querying Data

After Connecting from VBA, call the Select method to execute SQL SELECT statements.

public bool Select(string queryString, object paramNames, object paramValues)

The Select method returns a boolean indicating the query's success or failure and accepts the following parameters:


queryString	The query to execute; for example SELECT Id, FullName FROM Lead WHERE FirstName <> 'Bartholomew'
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

Accessing the Results

To return a field from the results, call the module's GetColumnName and GetValue methods. Call MoveNext to iterate over the results. The following example also contains some additional logic to write rows to the sheet.

List the columns in the first row of the spreadsheet:

Dim ColumnCount As Integer
ColumnCount = module.GetColumnCount
For Count = 0 To ColumnCount - 1
  Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
Next

Start outputting each row at row 2 of the spreadsheet. Additional logic checks for null values and date types.

Dim RowIndex As Integer
RowIndex = 2
While (Not module.EOF)
  For columnIndex = 0 To ColumnCount - 1
    If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
      Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
    Else
      Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
    End If
  Next
  module.MoveNext
  RowIndex = RowIndex + 1
Wend

Example

The following is a simple SELECT example. See Executing Parameterized Queries for an example macro that searches OData.

Sub DoSelect()
  On Error GoTo Error
  Dim module As New ExcelComModule
  module.SetProviderName ("OData")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray,valueArray

  Query = "SELECT Id, FullName FROM Lead LIMIT 10"
  module.SetConnectionString ("User=myuseraccount;Password=mypassword;URL=http://myserver/myOrgRoot;")
  If module.Select(Query, nameArray, valueArray) Then
    Dim ColumnCount As Integer
    ColumnCount = module.GetColumnCount
    For Count = 0 To ColumnCount - 1
      Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
    Next
    Dim RowIndex As Integer
    RowIndex = 10
    While (Not module.EOF)
      For columnIndex = 0 To ColumnCount - 1
        If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
          Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
        Else
          Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
        End If
      Next
      module.MoveNext
      RowIndex = RowIndex + 1
    Wend
    MsgBox "The SELECT query was successful."
  Else
    MsgBox "The SELECT query failed."
  End If
  Application.Cursor = Cursor
  module.Close
  Exit Sub
Error:
  MsgBox "ERROR: " & Err.Description
  Application.Cursor = Cursor
  module.Close
End Sub

Excel Add-In for OData

Querying Data

Accessing the Results

Example