How to Use QODBC Online with Microsoft Excel 2016 / 365 / 2013 32-bit

Setup QODBC to use with MS Excel

Note: QODBC allows you to quickly merge live QuickBooks Online data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please ensure you have installed the Microsoft Office Component named "Microsoft Query." Regarding MS Query, please refer to Microsoft Office with MS Query and QODBC for more information.

Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:

Office 2016:

Office 2013:

Office 2016:

Office 2013:

Create a blank workbook, or you can open an existing one. In this example, we are creating a new workbook:

An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "Get Data -> From Other Sources->From Microsoft Query."

Using Microsoft Query

It will take some time to get the DSN list:

Then, in the "Choose Data Source" Window, Select the QuickBooks Online Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.) Check to Use the Query Wizard to create/edit queries.)

For 64-bit MS Office, change the QRemote DSN to QuickBooks Online Data 64-bit, the default DSN created when installing QODBC Online.

Import QuickBooks Online Data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. Please select a table and the columns you want to input in the Choose Columns window. Multiple selections are available. Once a table is set, it is the default that all its columns are selected. You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. Please click the "Next" button:

This window allows you to sort your data. If you don't want to change the order, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Using Microsoft ODBC (Office 365)

Using "From ODBC"

From the list of DSNs, select "QuickBooks Online Data"

If prompted for credentials, enter the username "admin" and the Password "admin."

Ideally, no credentials are required. There is no need to input QuickBooks or QODBC credentials.

You can enter any username and password.

Import QuickBooks Online Data into MS Excel via Microsoft ODBC

Choose the table you want to import/link to Microsoft Excel from the list of tables.

Click the "Load" button to bring data to the Excel file.

Return Data to Microsoft Excel:

This method directly exports the data to Excel. You can view and edit these records, but the data is static, and modifications will not be uploaded into QuickBooks Online.

You can view the detailed connection properties by clicking the "Queries & Connections" button in the Data menu: 

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information: 

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, where you can view and edit data via SQL Statement. All modifications will be uploaded to QuickBooks.

Click the "SQL" button to view the SQL Statement of this query. The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only records with a Name equal to "Residential." Select the field header name and click on it, following the selection of Criteria -> Add Criteria. In the popped-out Add Criteria window, the button specifies the criteria Field as "Name " and Operator as "equals. " Then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks record:

When you finish the query, choose to save it so that you can use it directly next time. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or the External Data toolbar will update the data in the worksheet with the latest information from the linked QuickBooks Online tables, as shown above. Changes to the QuickBooks Online files will not affect this worksheet until you refresh the data with the Refresh option.

Tags: QuickBooks Online, QBO, Excel 365, MS Excel, Excel