Search              building menu display...

Microsoft Excel and QODBC

High bandwidth users, please see our flash video tutorial or Windows Media movie tutorial on 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.

 

---

So isn't it time you unleashed the full power of QODBC?

Like our FREE QODBC Basic Edition, you can also try our OBLIGATION FREE 30 Day QODBC Pro Edition Evaluation to see and write to QuickBooks Transaction Tables from a Web Server! Here are just some of the exciting features of using QODBC Pro Edition:-

• Access the Transaction Core of QuickBooks Accounting Data such as Invoices, Bills, Purchase Orders, Checks, Credit Memos, Estimates, Payments, Sales, Employee Time Tracking. Over 80 tables in all!*
• Generate Powerful QuickBooks Reports in your own applications such as Details on AR & AP, Aging Reports, Balance Sheet, Collections, Transaction Detail, 1099 Detail, Customer Balance Detail, Estimates, Expenses, General Ledger, Income Detail, Tax Detail, Inventory Detail, Item Profitability, Inventory Status & Valuation, Job Profitability, Open Invoices, Profit and Loss, Open PO's Sales by Customer, Sales Tax, Time by Job Trial Balance, Vendor Balance Detail & Summary. Over 75 reports total!*
• Get 30 days of telephone installation support.
• Get Discounts on future upgrades to QODBC and our other exciting products.
• Generate your own income by creating Access applications using the QODBC Pro Edition!

Download and Activate QODBC Pro Edition