Link to your data in Microsoft Excel using ODBC

From time to time, you may need to work or report on your data in a different way to how you do in Sage.

Connecting your Sage data to Microsoft Excel via the ODBC driver and a MS Query in Excel, gives you a powerful tool to investigation or summarise your data in a pivot table.  

Use this guide to set up your first connection and refresh your connections, if required. Let's take a look at how to do this.

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.
   NOTE: Enter your Admin logon details if prompted. 
5. In the ODBC Data source Administrator window, click the System DSN tab.
6. Click Add.
7. Scroll down to near the bottom of the list and double click on the latest version of Sage.
8. In the Data Source Name field, enter a brief version of your company name and the version number of Sage you're using.
9. 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
10. Click OK.
11. 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.
   NOTE: Enter your Admin logon details if prompted. 
5. In the ODBC Data source Administrator window, click the System DSN tab.
6. Double click the connection / company name that needs updating.
7. Delete the old information from the Data Path field.
8. In the Data Path field, enter the Data directory you noted in step 2.
9. 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 that you want to connect to your Sage 50 Accounts data.
2. From the menu bar, click Data.
3. Click  Get Data.
4. Click From Other Sources, then click From Microsoft Query.
5. Double click the connection / company name you want to investigate.
6. In the ODBC Connect window, enter your Sage 50 Accounts username and password into the User ID: and Password: fields respectively.
   NOTE: You don’t need to log out of Sage.
7. In the Query Wizard, select a data column you need in the Available tables and columns: section.
8. Click the right arrow  to add it to the Columns in your query: section.
   TIP: If you're not sure which columns you need to include, we've created this great interactive guide to help you.
9. Once you've added all of the data columns you need, click Next., then click Next, then click Next, then click Finish, then click OK.

That's it, 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.