Skip to content
logo Knowledgebase

Link to your data in Microsoft Excel using ODBC - 32-bit

Created on  | Last modified on 

Summary

How to connect to your 32-bit Sage 50 Accounts data through Microsoft Excel via ODBC.

Description

You can work or report on your data in a different way to how you do in Sage.

Connecting your Sage data via the ODBC driver gives you a powerful tool to investigate or summarise your data in Excel.  

The steps below help you set up your first connection and refresh your connections, if required.  

Follow our alternative article if you're using Sage 50 Accounts 64-bit.

Resolution

NOTE:

If you want to set up a Management or Turnover report, you can refer to our Reports available from the Excel Report Design Service article.

 

Connect for the first time or after upgrading Sage

  1. In Sage 50 Accounts, click Help, then select About.
  2. In the Program Details section in the About screen, note the Data Directory path.
  3. In the System Details section of the About screen, click the Window System Directory path.
  4. Find the odbcad32.exe file, then right-click it and choose Run as Administrator.
  5. Enter your Admin logon details if prompted. 
  6. In the ODBC Data source Administrator window, click the System DSN tab.
  7. Click Add.
  8. Scroll down to near the bottom of the list and double click on the latest version of Sage.
  9. In the Data Source Name field, enter a brief version of your company name and the version number of Sage you're using.
  10. In the Data Path field, enter the Data Directory path you noted in step 2.

    TIP:

    If using V28.1 or above, add ACCDATA to the end of the data field for example C:\ProgramData\Sage\Accounts\2022\Company.000\ACCDATA

     
  11. Click OK.
  12. Repeat steps 1-9 for each company you want to set up.

You've linked your accounts data to your Excel spreadsheet.

Refresh existing connection when data directory has changed

  1. Click Help, then click About.
  2. In the Program Details section, note the Data Directory path.
  3. In the System Details section, click the Window System Directory path.
  4. Find the odbcad32.exe file, then right-click it and choose Run as Administrator.
  5. Enter your Admin logon details if prompted.
  6. In the ODBC Data source Administrator window, click the System DSN tab.
  7. Double click the connection / company name that needs updating.
  8. Delete the old information from the Data Path field.
  9. In the Data Path field, enter the Data directory you noted in step 2.
  10. Repeat steps 1-8 for each company you need to update.

You've now refreshed the link between your accounts data and your Excel spreadsheet.

Create a new link to Excel

  1. Open a new Microsoft Excel worksheet, or the worksheet to connect to your Sage 50 Accounts data.
  2. From the menu bar, select Data then select Get Data.
  3. Select Legacy Wizards then From Microsoft Query (Legacy).

 

TIP:

If From Microsoft Query isn't available, follow the steps on our ERROR: '[08001] Cannot find all files in data path' article to access it.

  1. Select the Data Source to connect to and press OK.
  2. In the ODBC Connect window, enter your Sage 50 Accounts username and password into the User ID: and Password: fields respectively.

  3. NOTE:

    You don’t need to log out of Sage.

  4. In the Query Wizard, select the table in the Add tables window and press Add.
  5. Once you select the tables required, press Close.
  6. Form the table in the top left, select the variables to add to the Excel Workbook.
  7. Double-click the variables to add the column to the query.

TIP:

If you're not sure which columns you need to include, follow our interactive tables and variables used by Report Designer article.

 

  1. After adding the data columns required, go to File then select Return Data to Microsoft Excel.

This opens the Import Data window.

  1. Select how to view and where to locate this data in your workbook and press OK.

You've connected this Microsoft Excel worksheet to your Sage 50 Accounts data.

Refresh the connection in Excel

  1. Open your Microsoft Excel workbook.
  2. From the menu bar, click Data.
  3. Click Refresh All.

    NOTE:

    If any messages appear, action as appropriate.

      
  4. Click the machine Data source from the Select Data source window.
  5. Double-click the required connection / company name.
  6. Enter your Sage 50 Accounts username and password into the User ID: and Password: fields respectively, then click OK.
  7. If you have multiple connections, repeat steps 4-7.

You've now reconnected this Microsoft Excel worksheet to your Sage 50 Accounts data.