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, Location FROM Calendar WHERE Location <> 'Chapel Hill' |
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 Microsoft SharePoint.Sub DoSelect()
On Error GoTo Error
Dim module As New ExcelComModule
module.SetProviderName ("SharePoint")
Cursor = Application.Cursor
Application.Cursor = xlWait
Dim nameArray,valueArray
Query = "SELECT Id, Location FROM Calendar LIMIT 10"
module.SetConnectionString ("User=MyUserAccount;Password=MyPassword;Auth Scheme=NTLM;URL=http://sharepointserver/mysite;")
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