[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.
Excel - Customer & Company Tables - Download
Excel - Majority of the Tables and Reports - Download