This will enable you to create a direct connection from Excel to Sage 100 through an ODBC connection. You can pull data from specific data tables into a table format in Excel for filtering, sorting, organizing, and/or creating pivot tables.
First, you have to enable the Excel query program: Go to FILE > OPTIONS (on the bottom left side of the screen)
Choose the DATA tab located on the right-hand sidebar, check the box for "FROM MICROSOFT QUERY (LEGACY)", and then select OK.
From the top ribbon, select the DATA tab and then GET DATA.
In the drop-down menu, hover over LEGACY WIZARDS and then choose FROM MICROSOFT QUERY (LEGACY).
You will be prompted to choose a data source. The data source you should choose is SOTAMAS90*. Then select OK.
You will be prompted to log in to your company code.
Here is the QUERY WIZARD to walk you through creating the Excel query. This is where you can choose what data tables to pull data from. This is the hardest part of the process (knowing what tables hold what data). *IMPORTANT: Any table that ends with wrk or work is specific to Crystal Reports and will open blank.
The first 2 characters in these tables represent the module in which the table exists
You can choose as many tables as you want, and the query will automatically connect them. 3 is probably the maximum amount to pull that once; anything more will show blank or take a very long time to run.
Typically, a “Header” table goes along with a “detail” table.
Common tables to pull:
AR_invoiceHistoryHeader and AR_InvoiceHistoryDetail (this gives you all the Invoice Sales History)
GL_detailPosting (all GL transactions- this could take a very long time- limit the data range)
AR_OpenInvoice (Open Invoices/ Aging data)
When you choose a table, you can click the MINUS symbol to show all the fields in the table. You can either pull in the entire table or pull in specific fields, then click NEXT.
The query wizard will take you to a FILTER DATA screen. You do not need to enter anything in here. But would recommend entering filter criteria for large data sets.
Next, you will get a screen for SORT records. You do not need to enter anything here.
Here, you tell the wizard where you want your data to go. If you have no further changes to the tables you've selected, choose RETURN DATA to MICROSOFT EXCEL and select Finish.
It will ask you to log in again to Sage.
Now your data is in Excel for you to manipulate.
If you make a change to the data, it will NOT be written back to Sage.
If you right-click in the data table and choose REFRESH, it will pull in any new data. You can save this file to the network for everyone to use or just on your desktop. When you open it, you can refresh the data so it is up to date, no need to recreate it.If you need further assistance, reach out to RKL eSolutions Support
For more helpful tips and tricks about Sage 100, subscribe to our blog and stay up-to-date with our latest tutorials. We're dedicated to providing valuable resources for businesses looking to optimize their financial management with Sage Intacct.
What's New in Sage 100 Version 2026
How to Configure Sage 100 to Use OAuth Authentication
How to Distribute Reports Using Sage Intelligence for Sage 100 Users