CData Excel Add-In for FreshBooks 2019 - Online Help
Questions / Feedback?

Querying Data

CData Excel Add-In for FreshBooks 2019 - Build 19.0.7354

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 Email, Username FROM Clients WHERE Email = 'support@mycompany.com'
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 FreshBooks.
Sub DoSelect()
  On Error GoTo Error
  Dim module As New ExcelComModule
  module.SetProviderName ("FreshBooks")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray,valueArray

  Query = "SELECT Email, Username FROM Clients LIMIT 10"
  module.SetConnectionString ("OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;CallbackURL=http://localhost:33333")
  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) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0