[QODBC-Desktop] Using QuickBooks Data with Microsoft Excel 2016 / 365 / 2013 32-bit
Posted by Rajendra Dewani (QODBC Support) on 12 November 2012 01:51 PM
How to use QODBC with Microsoft Excel 2016 / 365 / 2013 / 2019 32-Bit
How to use QODBC with Microsoft Excel 2016 / 365 / 2013 / 2019 64-Bit (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)
QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.
QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.
Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to How to Configure QODBC to Work With 64-bit MS Excel.
Setup QODBC to use with Microsoft Excel
Note: QODBC allows you to quickly merge live QuickBooks 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 MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.
Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:
Create a blank workbook or 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."
It will take some time to get the DSN list:
And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to Use the Query Wizard to create/edit queries.).
The first time use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:
Select the "Yes, always" option, and then click the "Continue" button; in the next window, click the "Done" button to finish authorizing.
Import QuickBooks data into MS Excel via MS Query
After you grant permission for QODBC, you will be guided by Query Wizard. In Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And 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. If you don't want to put any questions, 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:
Return Data to Microsoft Excel:
This method will directly export the data to Excel, you can view and edit these records, but the data is static. The modification will not be uploaded into QuickBooks.
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, and you can view and edit data via SQL Statement there, and 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 the records with a Name having a value equaling "Residential." Select the field header name and click on it, following selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," 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 recordset:
When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:
Clicking Data -> Refresh Data or clicking the button in the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.
Excel - Customer & Company Tables - Download
Excel - Majority of the Tables and Reports - Download
Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016, Microsoft excel 2013 blank window