How to Use OLE in Exchequer to Pull and Push Data with Excel

If you’re using Exchequer and want more flexibility in your reporting, OLE (Object Linking and Embedding) is a powerful tool that allows you to pull live data into Microsoft Excel — and even push updates back into Exchequer.

In this guide, we’ll introduce how OLE works, how to build your first data query, and how to safely save changes back into your system.

What Is OLE in Exchequer?

OLE creates a live link between Exchequer and Microsoft Excel.

It allows you to:

• View sales and purchase balances
• Pull general ledger balances
• Report on stock, locations and jobs
• Access custom job figures
• Extract customer and supplier data

More importantly, certain OLE functions allow you to write data back into Exchequer.

For example, you can update:

• Stock descriptions
• Stock prices
• Budgets
• Nominal journals
• Address details
• Area codes

This makes OLE incredibly useful for bulk updates that would otherwise take hours inside Exchequer.

Before You Start: Install the OLE Add-In

To access OLE functionality in Excel, you must have the Exchequer OLE add-ins installed.

In Excel, you should see:

Add-Ins > Exchequer

If this option isn’t available, the add-ins will need to be installed. If you’re unsure how to do this, we have a separate help video to guide you through the process.

You’ll also need:

• Access to the OLE module in your Exchequer user permissions
• A valid login with appropriate rights

If you don’t have access, speak to your line manager to enable it.

Step 1: Start with Your Company Code

When building an OLE spreadsheet, always enter your company code in cell A1.

This code comes from the Multi Company Manager when you log into Exchequer.

Locking your spreadsheet formulas to this cell ensures everything references the correct company.

Step 2: Use the Data Query Wizard

To pull data into Excel:

1. Go to Add-Ins
2. Select Exchequer
3. Choose the type of data you want to retrieve

You can pull through:

• Cost Centres
• Customers
• Departments
• GL Codes
• Jobs
• Locations
• Stock
• Suppliers

For example, if you select Customers, the Data Query Wizard will:

• Ask which company you want to pull from
• Prompt you to log in
• Offer filtering options

You can choose to filter results or pull the full list.

Once complete, Excel will populate the customer account codes.

Step 3: Use OLE Functions to Pull Additional Fields

At this stage, you may only see customer codes — which on their own don’t tell you much.

To pull additional details:

1. Select Insert Function (fx)
2. Choose User Defined
3. Select the relevant Exchequer function
   
   

For example:

• Customer Name
• Address
• Balance
• Phone Number
• Default Cost Centre
  
  

You’ll need to supply:

• Company Code
• Customer Code
• (Sometimes) Line Number
  
  

Tip: Lock Your References

When building formulas:

• Use F4 to lock cell references
• Lock your company code to cell A1
• Lock the customer code column

This ensures that when you drag formulas down, Excel correctly references each row.

Where Do You Find the Right Function?

Exchequer includes a full OLE help library.

In Exchequer:

1. Go to Help
2. Select Help Contents
3. Open Exchequer OLE Help
4. Navigate to OLE Functions
5. Use Categorical Reference

Functions are grouped into:

• Customer Gets
• Supplier Gets
• General Ledger Gets
• Job Cost Gets
• Stock & Location Gets
• Saves (write-back functions)
  
  
  

Each function explains:

• What information it requires
• What it returns
• Any parameters (such as address line numbers)

This library allows you to build more advanced and tailored reports.

Writing Data Back into Exchequer (OLE Saves)

OLE doesn’t just pull data — it can update Exchequer too.

For example, you might want to update customer addresses.

To do this:

1. Insert a new User Defined function
2. Select the relevant “Save” function (e.g. customer save address)
3. Enter:
   • Company Code
   • Customer Code
   • Line Number
   • New Value Cell

Once the formula runs, the update is immediately written back to Exchequer.

If you check the customer record inside Exchequer, you’ll see the change has been applied.

Important Warning About OLE Saves

OLE save functions run automatically when the spreadsheet recalculates.

That means:

• If you reopen the spreadsheet later, the save function will run again
• This could unintentionally overwrite data

Best practice:
After running your updates, delete the save column to prevent it executing again.

For more advanced control, you can build toggle switches using IF statements — this is covered in more advanced OLE training.

Real-World Use Case: Bulk Price Increasess

A common use for OLE is updating stock prices.

Instead of:

• Opening each stock item
• Manually adjusting prices

You can:

1. Pull through all stock prices into Excel
2. Apply a formula (e.g. increase by 10%)
3. Use an OLE Save function
4. Update everything in minutes

This can save hours of manual processing.

Why Use OLE?

OLE is ideal for:

• Advanced reporting
• Bulk data updates
• Complex Excel-based analysis
• Faster administrative processes
  
  

Combined with Excel’s formulas and logic, it becomes a powerful reporting and data management tool.

Need Help with Exchequer OLE?

While OLE is incredibly powerful, it does require careful setup and understanding.

At The HBP Group, we can:

• Help you install and configure OLE
• Provide training on building reports
• Assist with bulk update processes
• Support advanced formula design

If you’d like to get more from your Exchequer system, contact us about OLE training.