Invoking UI Elements

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 DB2 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)

Update

Use the following methods to update at the worksheet or row level.

Function UpdateAll()

Saves all changed rows in the worksheet to the data source.

adxModule.UpdateAll

Function UpdateRows() As String()

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

Dim ret() As String
arr = adxModule.UpdateRows()

Function Update(Long() rowIndexes) As String()

Saves 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.GetUpdatedRows()
ret = adxModule.Update(arr)

Function GetUpdatedRows() As Long()

Returns the indexes of the modified rows in the sheet, as an array of longs.

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

Displaying Popup Messages

There is a boolean parameter called showPopups which is available in the following functions of adxModule.

adxModule.Update(indexes, [showPopups])
adxModule.UpdateAll([showPopups])
adxModule.VBAFunction([OtherParameters,...][showPopups])

When showPopups is set to True, the user is notified of any confirmation, error, warning, info popup messages. This parameter is false by default.

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()

Delete

Use the following methods to delete the selected rows or rows you specify by row index.

Function DeleteRows() As String()

Deletes 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.DeleteRows()

Function Delete(Long() rowIndexes) As String()

Deletes 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].

The following example deletes the first three rows (after the column headers in the first row):

Dim arr() As Variant
Dim arr(3) As Long
arr(1) = 2
arr(2) = 3
arr(3) = 4
ret = adxModule.Delete(arr)

UpSert

Use the following methods to update/INSERT at the worksheet or row level.

Function UpSertAll()