Excel Macro Example

Below is a sample of an Excel macro that accesses data from NetSuite. The ExcelComModule instance connects to the data source, executes SQL queries, and iterates over the results. The ExcelComModule class can connect to and perform data operations on any of the data sources that RSSBus exposes, so you will need to invoke the SetProviderName and SetConnectionString methods. After setting the provider and setting the connection string, the Select method executes a parameterized query. Each parameter, composed of a pair of names and values, needs to be stored in the arrays nameArray and valueArray. GetColumnName and GetValue are each used to return a field from the results. MoveNext iterates over the results of the query.

Sub DoSelect()
  On Error GoTo Error
  pId = InputBox("Id:", "Get Id")
  If pId = False Then
    Exit Sub
  End If
  Dim module As New ExcelComModule
  module.SetProviderName ("NetSuite")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray
  nameArray = Array("Idparam")
  Dim valueArray
  valueArray = Array(pId)
  Query = "SELECT InternalId, AcctName FROM Account WHERE Id = @myId"
  module.SetConnectionString ("Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;")
  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 = 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
    MsgBox "The SELECT query successful."
  Else
    MsgBox "The SELECT query failed."
  End If
  Application.Cursor = Cursor
  Exit Sub
Error:
  MsgBox "ERROR: " & Err.Description
  Application.Cursor = Cursor
End Sub