Execute Invantive SQL on Exact Online from Visual Basic for Applications (VBA)

Question

I want to run some Invantive SQL statements from within VBA code on Excel on an Exact Online data source.

I can use a UDF function call like:

I_SQL_SELECT_SCALAR("select fullname from me")

in an Excel sheet.

How can I retrieve the full name from within VBA?

Answer 1

First, you must reference the functions from Visual Basic Editor -> Tools menu -> References:

Then use code such as the following to retrieve a scalar value:

Option Explicit

Sub RunSql()
On Error GoTo Catch
Dim result As Variant
'
' Get full name of current user.
'
If Not I_INTEGRATION_ACTIVE() Then
MsgBox "VBA integration not available. Please use the Tools menu to activate it."
End If

result = InvantiveControlUDFs.I_SQL_SELECT_SCALAR("fullname", "Me")
MsgBox ("Result is '" & result & "'.")
'
' Retrieve scalar value: =I_SQL_SELECT_SCALAR(fieldName;tableName;whereClause;orderByClause;postFix;valueNoValue;valueTooManyValues;process)
' Retrieve table: =I_SQL_SELECT_TABLE(sqlStatement;errorOnMoreRows;errorOnMoreColumns;addHeaderRow;process)
' Normally disabled: =I_SQL_SCALAR(process;sql)
'

Finally:
    Exit Sub
Catch:
    HandleError "RunSql"
End Sub

Or to retrieve a result set (normally used in a matrix formula):

Option Explicit

Sub RunSqlTable()
On Error GoTo Catch
Dim result() As Variant
'
' Get list of GLAccounts.
'
If Not I_INTEGRATION_ACTIVE() Then
MsgBox "VBA integration not available. Please use the Tools menu to activate it."
End If

result = InvantiveControlUDFs.I_SQL_SELECT_TABLE("select * from exactonlinerest..glaccounts")
Finally:
    Exit Sub
Catch:
    HandleError "RunSqlTable"
End Sub

The use of I_SQL_SELECT_TABLE is only available in releases from October 2017 and newer.