Trainers Manual for Internal Use Only

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

Trainers Manual for Internal Use Only

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 1 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

OLE ......................................................................................................................................................... 3 Introduction ............................................................................................................................................ 3 Installing the Exchequer OLE Add-Ins in Microsoft Excel 2007 ............................................................ 4 Functions Summary..............................................................................................................................6 Using OLE Functions............................................................................................................................ 7 OLE Function Help .............................................................................................................................. 10 Example OLE Spreadsheets ............................................................................................................... 11 Using OLE with Customers................................................................................................................ 12 Using Data Query ................................................................................................................................ 16 Using OLE with the General Ledger.................................................................................................. 20 Creating a month by month Profit & Loss Account ............................................................................. 20 Creating a Profit & Loss spanning Periods & Years............................................................................25 Accounting Period Functions ............................................................................................................ 27 Profit & Loss ........................................................................................................................................ 27 Balance Sheet ..................................................................................................................................... 29 Using Cost Centres & Departments .................................................................................................. 34 Using EntDrill ...................................................................................................................................... 35 Using EntSelect ................................................................................................................................... 36 Using OLE Saves ................................................................................................................................ 38 Using If Statements ............................................................................................................................. 39 Aged Debtors Reports ......................................................................................................................... 44 Creating Transactions ........................................................................................................................ 46 StockTake ............................................................................................................................................ 49 OLE Error Messages ........................................................................................................................... 51

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 2 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

OLE

Introduction

Object Linking & Embedding (OLE), provides a link between Exchequer & Microsoft Excel.

Exchequer provides a library of functions available for use within Excel to provide instant access to information without the need to export & import, i.e. it is a live link.

There are two key OLE Functions:

OLE Gets, these Functions return the information from Exchequer to Excel for reporting information.

All installations have the relevant OLE Gets functions based on the modules purchased.

OLE Saves, these Functions send information from Excel to Exchequer, this could be to create or update information such as Budgets, Records or transactions.

OLE Saves are only available if they have been purchased, they will be based on the modules purchased.

OLE is a dynamic tool to return reporting information such as Balances, Statistics & update certain information in Exchequer, it is not a tool to use for transaction listing, if the user requires lists of transactions etc. they would use the Visual Report Writer.

Everything relating to OLE is driven by codes, starting off with the Company code, then the code of whatever is being used in the function such as a General Ledger, Customer or Stock code.

Records such as Customer, Suppliers & Stock cannot be created using OLE they must already be created directly on Exchequer. However, the majority information contained within the Records is available for updating.

The exception to this is Job Records which can be Created using OLE.

Transactions that can be created are: Nominal Journals Timesheets

OLE can only link to the currently configured installation of Exchequer.

There are many version releases of Microsoft Excel, the course will be based using Microsoft Excel 2010. Any changes relating to accessing certain features may vary but overall the workings of OLE remain unchanged.

Before any information is returned on Excel the user will need to log-in, depending on the users access levels only the relevant functions will return information.

There are currently over 800 OLE functions available. The course is designed to provide the user with an understanding of how OLE works, clearly not to cover every conceivable OLE function.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 3 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

Installing the Exchequer OLE Add-Ins in Microsoft Excel 2007

To install the Excel OLE Add-Ins in Excel 2010. Select the Office Button Select Excel Options Select Add-Ins Select the Manage Excel Add-ins (bottom of the screen) Select the Go... button This will open up the Add-Ins window, select Browse to go to the Exchequer main installation folder.

Within the main directory is a list of all the available functions, this list will vary based on what has been purchased.

Double clicking or highlighting & selecting the OK button will display the Microsoft Excel window asking if you want to Copy the file to the AddIns folder, the user must select No to this as it is important the file is upgraded with any future system upgrades which will only be included from within the main directory.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 4 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

Once No is selected the Add-In will be listed & selected in the Add-Ins window. When each Microsoft Excel Add-Ins files has been added to the list the Add-In installation is complete.

Each time Excel is opened the Add-Ins will be loaded, they will display on the toolbar at the bottom of the screen.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 5 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

Functions Summary

Within each of the installed Add-In files are a number of functions, each of which will be covered in the course, summary details are:

EntFuncs EntFuncs 2 EntGL EntGL2 EntStk EntStk2 EntData EntDrill

Contains functions relating to Customer, Suppliers, if applicable Job Costing information.

Only available if purchased. Allows updates to Exchequer relating to Customer, Suppliers Records, if applicable Job Costing information including creating Timesheets.

Contains functions relating to the General Ledger.

Only available if purchased. Allows updates to Exchequer relating to General Ledger, this includes the ability to create Nominal Journals.

Contains functions relating to Stock.

Only available if purchased. Allows updates to Exchequer relating to Stock Records, this includes the ability to populate the Stock Take screen. Contains database information such as Customer, Supplier, Stock lists which can be downloaded to Excel.

Allows Drill Down on data directly in Excel when applicable.

EntXApps

Contains functions relating to the Applications & Valuations module.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 6 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

Using OLE Functions

To access the list of installed Functions use the Function Wizard fx icon, which is available under

the Formulas tab or can be available on the toolbar.

Once selected the Insert Function window will display, all Exchequer functions are listed in the User Defined Category.

The list contains all available functions which are listed Alphabetically by code, the code always begins with Ent following which is the key data set the function belongs to which helps the user locate the correct area. For instance, EntCust, EntGL, EntJC, EntStk, EntSupp. The details of the currently highlighted function will be displayed in the bottom section of the screen. Each time the user selects OK on the first function after loading Excel the Exchequer OLE Server Login screen will display requiring the Exchequer log-in. Note: Depending on the version of Excel this sometimes automatically minimises on the task bar at the bottom of the screen rather than displayed as an opened window.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 7 of 52

Exchequer Induction Training Course ? Phase 4 __________________________________________________________________________________________

If the Cancel option is selected the Cancel Company Login window will display, if Yes is selected the OLE Server will close, selecting No will return the user to the Login screen.

This will load the OLE Server which will minimise on the toolbar.

If the user closes the OLE Server a Warning window will display, if Yes is selected the OLE Server will close, selecting No will return the user to the Excel.

__________________________________________________________________________________________ Exchequer Induction Course, V6.9, Revised July 2012Page 8 of 52

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download