Skip to main content

How To Connect Sage 100 to Excel

Pull data directly from Sage 100 into Microsoft Excel for business intelligence analysis without any 3rd Party Add-Ins.


By: Tekoa Software
Tekoa ERP


Sage 100 ERP MAS90 MAS200 data can easily be pulled directly into Excel.  Excel can perform this functionality without any third-party add-ins or tools.  Use the Excel built-in data tools to pull live Sage 100 ERP data into a spreadsheet, then analyzed the data and write reports using Excel.

This allows you to use Excel to:
  • Create Pivot Tables of Sage MAS data
  • Quickly Filter and Sort Sage 100 data
  • Easily Analyze data
  • Build Excel Worksheets that reference live Sage 100 ERP data
1. Open a new Excel spreadsheet and go to the Data tab.  Select the From Other Sources dropdown and select From Data Connection Wizard.


2. Select ODBC DSN.


3. Select SOTAMAS90, which is the Sage 100 ERP ODBC driver name.



4. Login to Sage 100 using credentials that have access to the ODBC files you want.


5. Select any Sage 100 datafile name as the database you want.

6. Uncheck "Connect to a specific table:" and click Next.

7. Click Finish

8. The Select Table window should open.  Select the Sage 100 table you want to pull into Excel.

9. At Import Data window, accept the defaults and click 'OK'.


10. Your Sage 100 ERP data for the table you selected will appear inside Excel.

Use the data in the spreadsheet as a source for analysis and reports written on other Excel sheets. 

To refresh the spreadsheet data, click the Refresh button on the Excel Data tab.


Couple tips...  First, in older versions of Sage 100 MAS90 MAS200, the ODBC driver is 32-bit only.  Which means this will only work if you are using a 32-bit version of Excel.  In newer versions of Sage 100, there is a 64-bit ODBC driver that will work with a 64-bit Excel.  Just keep in mind that if you have an older version of MAS, you will need a 32-bit version of Excel. 

Second, using Microsoft Query, you can customize the query that Excel pulls in if you want to link Sage 100 tables together and get more interesting datasets. 

Last, this method is great for bringing transaction history into Excel and then doing pivot tables and other detailed analysis on the data.

For more information about connecting Sage 100 to Excel, Web, SharePoint and SQL, contact Tekoa Software - ERP software for today's enterprise.



Comments

Popular posts from this blog

3 Ways Enterprise Systems Help Today's Business

By: Chase Ramey


Enterprise Systems(ES) are extremely important in the process view of organizations. They can help coordinate and facilitate communication throughout different functions as well exchange data across the process. Any function of a business, if it wishes to be successful, needs to be executed in a way that will not only be effective, but efficient as well. This is an important point to remember when looking at the different roles of enterprise systems in organizations. Executing the process, capturing and storing process data, and monitoring process performance are the roles that ES is extremely important to in an organization. But how is this done? What can ES do to perform these tasks in the best way possible for the company? To find out, we’ll look at each process individually.
Getting Things DoneWhen it comes to executing the process, Enterprise Systems play a vital role. These systems can inform employees when a task must be completed, provide the data and information…

Remote Desktop and Sage 100 Explained

Essential Insights Into Remote Desktop Terminal Services and Sage 100
By: Tekoa Software
Cloud Enable Your Enterprise


Sage 100 MAS 90 and MAS 200 from Sage Software are two of the most widely used mid size business management and accounting applications in service today. Both MAS 90 (standard) and MAS 200 (advanced) share the same end-user capability and functionality. The primary difference between MAS 90 and MAS 200 is the back-end technology. MAS 200 is a client-server application which works more efficiently than MAS 90 on wide area networks and large user counts.

One method gaining popularity among business that own Sage ERP 100 MAS 90 and MAS 200 is the use of Sage 100 Remote Desktop. There are a number of benefits to using Remote Desktop Services for Sage ERP 100 MAS 90/200. Here is a list of some of the benefits:

1. Improved Security Something your MAS consultant may not tell you is that Sage MAS 90 and MAS 200 require Full Access network security rights to not only the i…