Excel is a powerful tool that goes beyond simple spreadsheets, allowing users to connect and fetch data from various external sources, including ODBC (Open Database Connectivity) databases. ODBC is a standard interface for accessing data from different database management systems. By leveraging Excel’s built-in functionality, you can easily read data from other ODBC data sources. In this article, we will guide you through the simple steps to read data from Sage 100 via ODBC.

Step 1: Connect Excel to the ODBC Data Source

Open Microsoft Excel and navigate to the “Data” tab on the ribbon.

Click on “Get Data” in the “Get & Transform Data” section.

From the dropdown menu, choose “From Other Sources” and then select “From ODBC.”

A “Choose Data Source” window will appear, displaying the available ODBC data sources on your computer. Select the data source you set up in Step 1 and click “OK.”

Step 2: Import Data into Excel

After selecting the ODBC data source, you will be prompted to enter any necessary login credentials to access the database. Once connected, you can select the specific data you want to import.

Choose the table that you want to import by checking the respective boxes in the “Navigator” window.  (One table is recommended at a time for beginners.)

Click “Load” to import the data into Excel.

Excel will import the data and create a connection to the ODBC data source. This connection can be refreshed at any time to get the latest data from the external database without the need to re-import it.

Once loaded, you can save your Excel file for future use.

Step 3: Refreshing Data

To refresh the data ensure your Excel worksheet reflects the most recent data:

Data must be manually refreshed after the initial load.  Select any cell within the data range that you imported.

Navigate to the “Data” tab on the ribbon and click “Refresh All.”

Excel will retrieve updated data from the ODBC data source and update your worksheet accordingly.

By following these straightforward steps, you can seamlessly read data from other ODBC data sources in Excel. This functionality empowers you to access and analyze data from Sage quickly and easily.

How to Store Login Info to Avoid Login Prompts

Follow the below steps if you’d like to store the company code and login information to avoid the login prompts in Excel.

Open “Control Panel” on your computer.

Navigate to “Administrative Tools” and select “Data Sources (ODBC).”

In the “ODBC Data Source Administrator” window, select the SOTAMAS90 data source and click the Configure button.

On the Logon tab, enter the company code, user ID, and password and then click “OK” to save the data source.  (Note: You can test the connection to ensure it’s working correctly on the Debug tab.)