Use the CData.ExcelAddIn.ExcelAddInModule class to call the functionality available on the CData ribbon in your code.

Initializing the Module

Initialize the module with the following code. The module implicitly uses a connection that is linked to the worksheet. You link a worksheet when you click From Spark SQL on the ribbon and pull in data.

See Managing Connections to configure the linked connection.

Dim addin As COMAddIn
Dim adxModule As Object
Set addin = Application.COMAddIns.Item("CData.ExcelAddIn.ExcelAddInModule")
Set adxModule = addin.Object

Refreshing Data

Use the following methods to refresh your worksheets with the current data or revert your local changes.

Function RefreshAll()

Refreshes all worksheets in the workbook. Prompts the user to accept, as this overwrites local changes.

adxModule.RefreshAll

Function Refresh()

Refreshes the selected worksheet. Prompts the user to accept, as this overwrites local changes.

adxModule.Refresh

Function RevertRows() As String()

Reverts the selected rows. Returns an array of success or failure values, in the form [SUCCESS|ERROR][:Error Message].

Dim arr() As Long
Dim ret() as String
ret = adxModule.RevertRows()

Function Revert(Long() rowIndexes) As String()

Reverts the rows at the row indexes you specify. Returns an array of success or failure values, in the form [SUCCESS|ERROR][:Error Message].

Dim arr() As Long 
Dim ret() as String
arr = adxModule.GetUpdatedRows()
ret = adxModule.Revert(arr)

INSERT

Use the following methods to insert the selected rows or the rows at the row indexes you specify.

Function InsertRows() As String()

Inserts the selected rows. Returns a string array of success or failure values for each row, in the form [SUCCESS|ERROR][:Error Message].

Dim ret() As String
ret = adxModule.InsertRows()

Function Insert(Long() rowIndexes) As String()

Inserts the rows at the row indexes you specify. Returns a string array of success or failure values for each row, in the form [SUCCESS|ERROR][:Error Message].

Dim arr() As Long 
Dim ret() as String
arr = adxModule.GetInsertedRows()
ret = adxModule.Insert(arr)

Function GetInsertedRows() As Long()

Returns the indexes of the rows added to the worksheet, as an array of longs.

Dim arr() As Long
arr = adxModule.GetInsertedRows()