Knowledgebase: Microsoft Products
[QODBC-Desktop] How to Use QODBC with Microsoft Excel 2007
Posted by Rajendra Dewani (QODBC Support) on 23 August 2012 10:07 AM

How to Use QODBC with Microsoft Excel 2007

Setup QODBC to work with Microsoft Office 2007

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 data in the worksheet to the most current data in the linked QuickBooks table.

Before using MS Excel application, please make sure you have installed MS Office Component named "Microsoft Query". About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

First please select the data input method via Data->From Other Sources->From Microsoft Query

 

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 Use the Query Wizard to create/edit queries.).

 

The first time use of MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select "Yes, always" option, and then click "Continue" button, in the next window, click "Done" button to finish authorizing.

Then you will be guided by Query Wizard. In Choose Columns window, select a table and its columns you want to input. Multiple select is available. And once a table is selected, it is the default that all its columns are selected. You can add or remove columns from the right list "Columns in your query"

 

Next screens allow you to filter and sort the data to be imported, and then you will reach the finish screen.

 

 

Now the Query Wizard is moving to the final step.

 

Return Data to Microsoft Office Excel will directly export the data to Excel, you can view and edit these records, but modification will not be uploaded.

 

View data or edit query in Microsoft Query will go to MS Query window and you can view and edit data via SQL Statement there, and all modification will be uploaded to QuickBooks.

 

You could use SQL Statement to build your query, press "SQL" button at the top of the window to enter SQL view.

 

 

 

The desired recordset is to contain only the records with a Name having a value equaling "Residential". Select the field header name and click on it, next select Criteria -> Add Criteria. In the poped out Add Criteria window, worksheet specifies the criteria Field as "Name", Operator as "equals", then click 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:

 

Once you have done, click File->Return Data to Microsoft Office Excel

 

Then the will be displayed at your worksheet.

 

 

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

 

Also, Refer:

Using QuickBooks Data with Excel 2010 32-bit
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

(0 vote(s))
Helpful
Not helpful

Comments (2)
Lavigne
20 September 2012 03:18 PM
Thanks for srhaing. What a pleasure to read!
M.Yousuf Khan
14 March 2016 11:04 AM
Valuable information.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).