Macro (VBA) Functions

Excel macros can implement the RSSBus.ExcelAddIn.ExcelComModule class to execute SQL commands against NetSuite data, call stored procedures to invoke NetSuite-specific actions, and traverse the returned results.

Class ExcelComModule

The ExcelComModule implements the following methods:

public void SetProviderName(string providerName)

This sets the provider for which the instance exposes functionality. The provider name for the RSSBus Excel Add-In for NetSuite is NetSuite.

public void SetConnectionString(string connectionString)

This sets the connection string required to connect to NetSuite; e.g., Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;.

public void Open()

This opens a connection. Note that calling Open is optional, as the call to Select, Insert, Update, or Delete will open the connection if it is not already open.

public bool Select(string queryString, object paramNames, object paramValues)

This executes a SELECT query. It returns true on success, false otherwise.

queryString	The query to execute; e.g., SELECT InternalId, AcctName FROM Account.
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

public bool Insert(string queryString, object paramNames, object paramValues)

This executes an INSERT query. It returns true on success, false otherwise.

queryString	The query to execute; e.g., INSERT INTO Account (AcctName) VALUES ('Petty Cash').
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

public bool Update(string queryString, object paramNames, object paramValues)

This executes an UPDATE query. It returns true on success, false otherwise.

queryString	The query to execute; e.g., UPDATE Account SET (AcctName='Petty Cash') WHERE Id = 'Id'.
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

public bool Delete(string queryString, object paramNames, object paramValues)

This executes a DELETE query. It returns true on success, false otherwise.

queryString	The query to execute; e.g., DELETE FROM Account WHERE Id = 'Id'.
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

public bool CallSP(string queryString, object paramNames, object paramValues)

This calls a stored procedure. It returns true on success, false otherwise. You can pass the entire stored procedure command to CallSP as text using the syntax:

 CallSP("EXECUTE my_proc @second = 2, @first = 1, @third = 3;")

You can also pass the name and value pair of each parameter to CallSP as two arrays. This is useful when working with many parameters and XML aggregates. In this case, the query string argument to CallSP must be the name of the parameter:

 CallSP("GetRoles",nameArray,valueArray)

public void Close()

This closes an existing connection.

public int GetColumnCount()

This gets the number of columns available in the result.

public string GetColumnName(int index)

This gets the name of the column at the specified index.

public int GetColumnType(int index)

This gets the type of the column at the specified index. Performs a cast of the VBA type of the column to an int.

public object GetColumnValue(int index)

This gets the value of the column at the specified index.

public object GetValue(int index)

This returns the value of the column at the specified index.

public bool MoveNext()

This moves to the next row. It returns false if the current row is the last row.

public bool EOF()

This checks the current position of the row cursor. It returns true if the cursor has reached the end, false otherwise.