This article describes how you can define your own custom Excel formulas in VBA to exchange data with Invantive SQL supported platforms.

Instructions

Perform the following steps to add your custom Excel formula to an Invantive Control spreadsheet using VBA:

  • Open the spreadsheet attached.

  • Or: create a new spreadsheet using File → New.

  • Log on to your desired database, such as Exact Online.

  • Activate the Invantive Control for Excel repository using the utmost left slider in the Modeler ribbon.

  • Go to the Developer ribbon.

  • Create a module in your spreadsheet.

  • Paste the following code in the code block:

'
' Retrieve the name of an Exact Online company using a custom Excel formula.
' Illustrates the use of Invantive SQL from within VBA.
'
Public Function MyGetDivisionName(division As String) As String
    On Error GoTo Catch

MyGetDivisionName = I_SQL_SELECT_SCALAR("Description", "SystemDivisions", "Code = " + division)

Finally:
   Exit Function
Catch:
   HandleError "MyGetDivisionName"
End Function
VBNET
  • Adapt where necessary.

  • Go to the Excel sheet again.

  • Enter an Excel formula in a cell:

=MyGetDivisionName(102673)
TEXT
  • Of course you can replace the formula parameters by range references.

  • Watch the results appear.

Of course you will need to make sure the Invantive SQL constructed is valid and not sensitive for SQL injection, but these are general engineering issues.