Integrating Sage 50 with MS Excel
You may have had occasions where you need to produce a report from your Sage Accounts and found there isn’t one available, or you want to produce your own bespoke report. You may have tried to produce the report using Sage Report Designer, but found it all a bit daunting when you first open it. One option is to get the data into Excel.
Over the past few years Sage have realised there may be limitations in producing reports directly from the software and have worked on improving the integration between Sage 50 and Excel.
If you want to get your data into Excel, there are 3 options available;
- Send transaction to Excel
- Excel Integrated Report
- Use of ODBC driver
Send transactions to Excel
From version 8 Sage have included an option in the main menu, File > Integration > Contents to Microsoft Excel to allow the current data on screen to be sent. This option can be used with any active screen.
As Sage 50 has developed over the years this feature has been brought to the front a bit more with the addition of To Excel button on a number of screens.
Excel Integrated Reporting
Excel Integrated Reporting installs on your computer along with Sage Accounts, so you can start using it straight away. In Excel you may have noticed and additional Sage tab or additional features on the Add-Ins tab.
Excel Integrated Reporting has two basic components:
- Sage reports – These are reports that you can run to extract data straight onto your worksheet. Simply select the report you want to include in your worksheet, for example, Balance Sheet, and Excel Integrated Reporting inserts the appropriate Sage Accounts data starting from the selected cell. This is an easy way to get large amounts of data from Sage Accounts into your Excel worksheet, including standard Sage reports or your own custom reports.
- Sage functions – You can use these functions on your worksheet when you want to include individual values from your accounts data. For example, the SgAccBankBalance function displays the balance of a specified bank account on your worksheet.
Use of ODBC driver
To help you transfer data quickly, easily and accurately, you can use the Sage 50 Accounts ODBC driver to read your accounts software data directly from applications such as Excel.
ODBC is a software standard developed by Microsoftand stands for Open DataBase Connectivity. An ODBC driver translates the data files from one application, for example Sage 50 Accounts, so that they can be read by a Windows application that supports ODBC, such as Excel.
The ODBC driver is read only, so you cannot write back to your software data files.