Excel Add-In for Microsoft SharePoint

Build 24.0.8963

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:

queryStringThe query to execute; for example SELECT Id, Location FROM Calendar WHERE Location <> 'Chapel Hill'
paramNamesAn array with the named parameters used in the query. Required if using a parameterized query.
paramValuesAn 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

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963