Insert Invoices with the Excel Add-In for QuickBooks

The CData Excel Add-In makes it easy to edit and save data from company files and QuickBooks Online companies. When you retrieve data, the resulting spreadsheet is linked to the live data. You can then refresh data, insert, update, and delete from the CData tab on the ribbon. This makes it easy to insert Invoices and other QuickBooks entities, such as PurchaseOrders, SalesOrders, etc.

An Invoice is similar to many other QuickBooks entities in that it has a one-to-many relationship with its Line Item table, InvoiceLineItems. The technique used in this article can be used with any line items table. You will link a spreadsheet to the InvoiceLineItems table, fill in a new row of line item data, and insert the new Invoice.

Establish a Connection

Before getting started, you will need to establish a connection to QuickBooks. Click the From QuickBooks button on the CData tab to launch the Data Selection wizard. To enable updates for the connection, add a connection to QuickBooks with the "Update" option checked. See the "Getting Started" chapter in the help documentation for a step-by-step guide to establish a connection to local and remote company files and QuickBooks Online companies.

Link a Spreadsheet to InvoiceLineItems

Retrieve data from the CData tab links the spreadsheet to the live QuickBooks data. To retrieve the data, click From QuickBooks to open the Data Connection Wizard. In the wizard, you can generate the underlying query to request data from QuickBooks. If you want to insert data, you only need to retrieve one row. This example uses the following query:

SELECT * FROM InvoiceLineItems LIMIT 1

Insert InvoiceLineItems

To create a new invoice, add at least one line item. Follow the steps below to insert a line item to the InvoiceLineItems table:

1. After you have linked the InvoiceLineItems table to a spreadsheet, you can add line item data in a row directly below the last result. You must specify the following Line Item information: Line_DetailType, Line_SalesItemLineDetail_ItemRef, Line_Amount, and CustomerRef. Also, for certain items, Line_SalesItemLineDetail_Qty may be required.
2. When you input data on the new row, you will notice that the text changes to red. Select the row when you are done and click the "Update Rows" button to insert the new Invoice.
3. After inserting the Invoice, the unique identifier of the newly created invoice will be returned. If you need to add additional line items to the invoice, simply copy the InvoiceId into new rows below the Invoice you just inserted. When you are done, select all of the rows and click "Update Rows".

Excel Add-Ins for 250+ Data Sources

For more information on CData's suite of Excel Add-Ins, visit our Excel Add-Ins page.

Download a free 30-day trial of the CData Excel Add-In for QuickBooks and get simplified access to your data today.