Board macros functions for Excel and Word

When the Board Office Add-in are installed, it is possible to use the following function in Excel and Word macros.

Opens the connect dialog (is equivalent to clicking the Connect button in the ribbon bar):

void ShowConnectDialog();

Opens the Select window (is equivalent to clicking the Select button in the ribbon bar):

void ShowSelectDialog();

Setting a selection (the propertyContainer is the WorkBook where to set the selection):

void Select(string dbname, string Coordinates, object propertyContainer);

Execution of the connection with username/password (without opening the login dialog):

bool Connect(string host, int port, string user, string password, bool useSSL, bool useWinEncryption);

Execution of the connect with SSO (without opening the login dialog):

bool Connect(string host, int port, bool useSSL, bool useWinEncryption);

Execution of the disconnect:

void Disconnect();

Refresh data:

void Refresh();

Executes a refresh of the current Sheet:

void Refresh();

Change the save/undo mode:

void SetSaveUndoMode(bool value);

Saves data to the server (equivalent to clicking the Save button of the ribbon bar):

void SaveDataOnServer();

Function returning the status of the connection:

bool IsConnected();

Function to retrieve the list of items descriptions of an entity:

string[,] GetElements(string database, string entityName);

Function to retrieve the list of items codes of an entity (available from version 8.0.1):

string[,] GetElementsCodes(string database, string entityName);

Function to retrieve the list of items codes and descriptions  of an entity (available from version 8.0.1):

string[,] GetElementsCodesAndDescriptions(string database, string entityName);

Function to execution a script (i.e. script of a layout definition):

object[,] ExecuteScript(string script);

Note:

To view a script open the client and go to the "Board Options" window, enable the option

Code Samples:

Silent (with no dialog) connection:

Sub DoConnect()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    If automationObject.Connect("Local Engine", 0, Nothing, Nothing, False, True) Then

        MsgBox "Connected"

    Else

        MsgBox "Not Connected"

    End If

End Sub

Check if the user is connected to the Board Server:

Sub IsConnected()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    If automationObject.IsConnected Then

        MsgBox "Connected"

    Else

        MsgBox "Not Connected"

    End If

End Sub

Open the selection window:

Sub DoSelect()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    automationObject.ShowSelectDialog

End Sub

Set a new Workbook selection:

Sub DoSelectScript()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    automationObject.Select "Delta", "Year=2005; division=accessories", ActiveWorkbook

End Sub

Perform the save operation on the server:

Sub DoSave()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    automationObject.SaveDataOnServer

End Sub

Get entity elements:

Sub GetElements()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    Dim result() As String

    result = automationObject.GetElements("Delta", "Product")

    Dim size

    size = UBound(result)

    With ActiveSheet

        Range(.Cells(10, 2), .Cells(size + 10, 2)).Value2 = result

    End With

End Sub

Execute a script and show the result:

Sub ExecuteScript()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    Dim result() As Variant

    result = automationObject.ExecuteScript("Database ""Delta""" & vbCrLf & "Select" & vbCrLf & """Year"" ""2003"" ""2004"" ""2005""" & vbCrLf & "End Select" & vbCrLf & "Table""Titolo Layout"" ShowHeaders ShowHorizontalGrid ShowVerticalGrid Indent 10" & vbCrLf & "ByRow ""Division"" ""Product Group"" ""Product"" HideEmptyRows" & vbCrLf & "ByColumn ""Year"" ""Quarter""" & vbCrLf & "Show ""Gross Sales""" & vbCrLf & "End Table")

    Dim size

    size = UBound(result, 1)

    Dim size2

    size2 = UBound(result, 2)

    With ActiveSheet

        Range(.Cells(10, 2), .Cells(size + 10, size2 + 2)).Value2 = result

    End With

End Sub

Set the save/undo mode:

Sub SaveUndoMode()

    Dim addin As Office.comaddin

    Dim automationObject As Object

    Set addin = Application.COMAddIns("BoardExcelAddIn")

    Set automationObject = addin.Object

    automationObject.SetSaveUndoMode (True)

End Sub