Link to your data in Microsoft Excel using ODBC on a 64 bit computer

How to connect to your Sage 50 Accounts data through Microsoft Excel via ODBC on a 64-bit computer.

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 via the ODBC driver gives you a powerful tool to investigate or summarise your data in Excel.  

Use this guide to set up your first connection and refresh your connections.

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

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 Directory link, then open the System32 folder.
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 then add ACCDATA to the end of the data field for example C:\ProgramData\Sage\Accounts\2022\Company.000 will become C:\ProgramData\Sage\Accounts\2022\Company.000\ACCDATA.
10. Leave the checkbox blank, then click OK.
11. Repeat steps 1-9 for each company you want to set up.

That's it, you've linked your accounts data to your Excel spreadsheet.

---

Refresh the existing connection when the 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. Move up one folder to the Windows folder and open the System32 folder.
5. Find the odbcad32.exe file, then right-click it and choose Run as Administrator.
   NOTE: 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 to update.
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 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 interactive guide to help you.
9. Once you've added all 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.