QODBC Driver for QuickBooks Enterprise Solutions by FLEXquartersMicrosoft Excel Setup
How to set up ODBC access Microsoft Excel.
Creating a link to live QuickBooks data in Excel
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 Microsoft Excel, you must ensure that the Microsoft Query Add-on is installed. See the Microsoft Office 2000 topic.
Select the menu “Data / Get External Data / New Database Query”. The following Data Source screen is available:
Select the Data Source that is currently opened in QuickBooks and click “OK”.
Choose the table you wish to import, and select the columns from the table to import by pressing the “>” button. The next screens allow you to filter and sort the data to be imported, and then you will reach the finish screen.
You can return the data to Excel at this point or as in the above screen, opt to edit the data in MS query and apply additional criteria to the QuickBooks recordset. Click “Finish”.
The desired recordset is to contain only the records with a “Customer Type” having a value equaling “Commercial”. Select the field header name and click on it, it will then highlight the entire column. Next select “Criteria / Add Criteria”, from the list of values (Residential, Commercial) select “Commercial” /Add.
The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:
Select File / Return Data to Microsoft Excel, the following screen will prompt you for the destination of the data:
Click “OK” and the data will be populated into the existing Excel worksheet.
The data from your QuickBooks table is now included in your worksheet and the data remains connected to the QuickBooks table from which it was imported
Clicking Data / Refresh Data 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.
Copyright © 2005 FLEXquarters.com LLC All rights reserved