Excel Link – SapphireOne Integration Guide

Prerequisites

Before installation confirm:

• The required DLL files and Excel add-in (.XLA) files are available
• The IP address of the SapphireOne server is known
• The SapphireOne Gateway Port number is known

The port number is configured in:

Utilities Mode > Utilities Controls > Utilities Default Settings > Utilities Remote Access Defaults

Step 1 Copy DLL Files

Copy the following files:

PCEVENT.DLL
DELPHIMM.DLL

Place them in the same directory as:

Excel.EXE

Typical location

Program Files → Microsoft Office → Office

Note
Folder names vary depending on the Office version installed.

Developer note
These DLLs handle the TCP/IP communication between Excel and the SapphireOne Gateway.

Step 2 Copy the Excel Add-in

Copy the file

SapphireOne TCP V1-0.XLA

Into

Program Files → Microsoft Office → Office → Library

Step 3 Install the Add-in

Open Microsoft Excel

Navigate to

Tools → Add-in Manager

Select Browse

Open

SapphireOne TCP V1.0

Excel will now register the SapphireOne functions in the Excel Function Manager.

Step 4 Enable the Add-in

Navigate to

Tools → Add-Ins

Enable

SapphireOne TCP V1-0

Step 5 Configure TCP Settings

Open the Excel Function Wizard.

Scroll to the category

User Defined

Select

Config

Enter the following values

Result

Excel is now able to execute SapphireOne Gateway commands.

Install Excel Add-ins

Open Microsoft Excel.

Navigate to

Tools → Add-ins

Select and open

S7PX

Then select and open

SapphireOneV6-Addin

Ensure both add-ins are enabled.

Enable Apple Events in SapphireOne

Open SapphireOne Single User.

Navigate to

Utilities Mode > Utilities Controls > Utilities Default Settings > Utilities Remote Access Defaults

Enable

Apple Events (Direct Access)

Result
Excel can now execute SapphireOne Gateway commands via Apple Events.

Developer note
Apple Events is the inter-process communication method used by macOS to allow applications to send commands to each other.

SapphireOne Configuration

Open SapphireOne.

Navigate to

Utilities Mode > Utilities Controls > Utilities Default Settings > Utilities Remote Access Defaults

Enable

• Apple Events
• Guest access allowed in User Settings

Excel Add-ins

Open Excel → Tools → Add-ins

Load

S7PX

Then

SapphireOneV6-CS-Addin

Ensure both are enabled.

Additional Requirements

The following conditions must be met:

• Program sharing enabled
• Excel and SapphireOne must run on the same Mac

This is a requirement of the Mac Client/Server integration model.

GLBalanceS (Simple)

Purpose
Returns the balance for a GL account in the current year.

Syntax

GLBalanceS(GL Account, Period)

Examples

GLBalanceS(“01000-00”,1) → 36,000
GLBalanceS(“01000-00”,-1) → 60,000
GLBalanceS(“01@”,1) → 82,000
GLBalanceS(“01@-00”,-1) → 140,000

Developer note
This is the most commonly used Excel Link command for financial reports.

GLBalanceL (Long)

Purpose
Returns balance by year and actual/budget type.

Syntax

GLBalanceL(GL Account, Period, Year, AB)

Parameters

Example

GLBalanceL(“01000-00”,1,0,0)

GLBalanceFL

Purpose
Returns balances across a fixed period range.

Typical uses

• quarterly reporting
• balance sheet aggregation

Syntax

GLBalanceFL(GL Account, StartPeriod, Year, AB, FinishPeriod)

Example

GLBalanceFL(“01000-00”,0,0,0,12)

GLBalanceFS

Purpose
Returns balances between two periods.

Syntax

GLBalanceFS(GL Account, FromPeriod, ToPeriod)

Example

GLBalanceFS(“01000-00”,1,2)

GLBudgetWrite

Purpose
Writes a budget value.

Syntax

GLBudgetWrite(GL Account, Period, Amount, YesNo)

Example

GLBudgetWrite(“01000-00”,1,60000,1)

Developer note
This function modifies data in SapphireOne. Ensure correct permissions exist before enabling write operations.

Purpose
These commands return the name associated with a SapphireOne ID.

They are useful because names may change over time but IDs remain constant.

GLNameFind(General Ledger ID)

Example

GLNameFind(“01000-00”) → Sales-A

CLNameFind(Client ID)

Example

CLNameFind(“ABC”) → ABC Client

VENameFind(Vendor ID)

Example

VENameFind(“VEN”) → Vendor-A

INNameFind(Inventory ID)

Example

INNameFind(“PRODA”) → Product-A

PRNameFind(Project ID)

Example

PRNameFind(“PRA”) → Project-A

Developer note
These functions are frequently used alongside reporting formulas to display readable descriptions.

Performance Note

InventoryA and InventoryB may take longer to return results due to the number of records scanned.

Large installations with high transaction volumes may experience slower queries.

InventoryA

Purpose
Returns aggregated values for inventory transactions.

Syntax

InventoryA(RepID, InventoryID, Period, Type, TranType, Project, Company)

Parameters

Rep ID
Salesperson ID

Inventory ID
Product ID

Period

-1 current year
-2 all records

Type values

1 total value
2 total cost
3 margin
4 total quantity
5 maximum value
6 minimum value
7 average value
8 maximum cost
9 minimum cost
10 average cost
11 maximum margin
12 minimum margin
13 average margin
14 maximum sale price
15 minimum sale price
16 average sale price

Transaction type options

InventoryB

Purpose
Extended filtering version of InventoryA.

Additional filters include:

• Client
• Client class
• Client area
• Inventory class
• Project class

Syntax

InventoryB(RepID,ClientID,ClientClass,ClientArea,InventoryID,InventoryClass,Period,Type,TranType,Project,ProjectClass,Company)

Example

InventoryB(“”,””,””,””,”3010-6.00~@”,””,11,”1″,”S”,””,””,”0″)

Wildcard support

@

Build flag

B = builds only

Returns negative values when inventory is consumed as a component.

[TOGGLE: END]

[TOGGLE: START InventoryC Command]

Purpose
Returns status values for an inventory item.

Syntax

InventoryC(InventoryID,InventoryClass,Type)

Type values

1 Current
2 On Order
3 Backorder
4 Allocated
5 Opening
6 Standard Price
7 Average Cost
8 Last Cost
9 Sales MTD Qty
10 Sales MTD Value
11 Purchases MTD Qty
12 Purchases MTD Value
13 Not listed in original table
14 Sales TTD Qty
15 Sales TTD Value
16 Purchases TTD Qty
17 Purchases TTD Value
18 Duplicate entry in original documentation
19 Order Quote Qty
20 Order Quote Value
21 Purchase Quote Qty
22 Purchase Quote Value
23 Price A
24 Price B
25 Price C

Developer note
InventoryC is often used for operational dashboards.

Purpose
Returns the value of a specific field in an inventory record.

Syntax

InventoryS(InventoryID, FieldNumber)

Developer note
InventoryS acts as a direct field reader for the inventory table.

To make the large field list easier to navigate it has been grouped into logical categories.

Additional Inventory Fields

Prices Sub
Notes
Analysis
MTD Sales Qty
MTD Sales Val
MTD Purch Qty
MTD Purch Val
MTD COS
TTD Sales Qty
TTD Sales Val
TTD Purch Qty
TTD Purch Val
TTD COS

User Defined Fields

UDF1
UDF2
UDF3
UDF4

Extended Metadata

Keywords
Tax Code
Tax Rate
Later6

Additional Reporting Fields

MTDB Sales Qty
MTDB Sales Val
MTDB Purch Qty
MTDB Purch Val
MTDB Cos
TTDB Sale Qty
TTDB Sales Val
TTDB Purch Qty
TTDB Purch Val
TTDB COS

System Fields

INTERNALS
Include Sales
TableA
TableB
TableMaster

Advanced Fields

UPC
Second ID
Calc
Write
Pict

Inventory Workflow Fields

Order Value
Back Value
Ord_Quote_Qty
Ord_Quote_Val
Pur_Quote_Qty
Pur_Quote_Val

Product Structure Fields

KitItem
Reorder_Qty
Kit_Style
Kit_Prices
Kit_Show_Lines
Kit_Show_Prices

Tax Fields

Tax_V_Code
Tax_V_Rate
Tax_V
Tax_Rules

Pricing Control

Dec_Prices
Whole_Price_Code
Price_Exempt

Miscellaneous Fields

Notional
Not_Type
Not_Value
CalcFormula
Multiplier
Multiplierask

Serialisation

Serialize
SerPrefix
SerFormat

Vendor Fields

Ven_InvID
Ven_Inv_Desc

Additional Control Fields

Aval_Stock
Tax_Incl
Build_Via_BGJ
Batch_Expiry
On_Cost_Factor
Tariff
DutyAble
MasterTable

TIMEA

Purpose
Returns values for timesheet entries.

Syntax

TIMEA(ProjectID,ResourceID,Activity,Period,Type,Completed)

Parameters

Type values

-1 total charge
2 total cost
3 total quantity

Developer note
This command is commonly used for project profitability analysis.