[QODBC-Desktop] QODBC and Microsoft Excel
Posted by Juliet on 05 January 2010 05:31 AM
QODBC and Microsoft Excel
Microsoft Excel 2010 and QODBC
For MS Access 64-Bit, Please refer: How to Configure QODBC to Work With 64-bit MS Excel
Setup QODBC to use with MS Excel 2010
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.
Before starting, please make use you have the 32-bit version of MS Excel:
First, please select the "Data" tab, Navigate to "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.).
'QuickBooks Data' as an example:
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.
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, select a table and its columns you would like to import. Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here I choose table Customer as a sample.) You can add or remove columns at the right list "Columns in your query"
You can set a filter for your query in this window, if you don't want to set any query, please click "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, modification will not be uploaded into QuickBooks.
You can view the detailed connection properties by clicking "connection" button in Data menu:
In the Workbook Connection Window, click "Properties" button to view detailed information:
Now you can view the connection string and its SQL statement in definition window:
View Data or Edit Query in MS Query:
This method will lead you to MS Query window and you can view or edit data via SQL Statement.
Click "SQL" button to view 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 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 popped out Add Criteria window specify 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:
When you finish the query, choose to save, so that next time you can directly use the same query:
Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:
Clicking Data -> Refresh Data or click the button in External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables as shown above. Changes made to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.