[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 worksheet to the most current data in the linked QuickBooks table.
Before using the 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 to Use the Query Wizard to create/edit queries.).
The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:
Select the "Yes, always" option and click the "Continue" button. In the next window, click the "Done" button to finish authorizing.
Then 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."
The following 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 modifications will not be uploaded.
View data or edit query in Microsoft Query will go to the MS Query window, and you can view and edit data via SQL Statement there. All modifications will be uploaded to QuickBooks.
You could use SQL Statements to build your query. Press the "SQL" button at the top of the window to enter the SQL view.
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, the worksheet specifies 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:
Once you have done this, click File->Return Data to Microsoft Office Excel.
Then they will be displayed on your worksheet.
Excel - Customer & Company Tables - Download
Excel - Majority of the Tables and Reports - Download