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 CustomerName and some Line Item information: The ItemName and ItemQuantity are 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 "Insert 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 "Insert Rows".
 
 
Downloads