Insert Invoices with the Excel Add-In for QuickBooks

In this article we will show how to insert an Invoice into QuickBooks within Excel. The RSSBus Excel Add-In makes this easy by linking a spreadsheet with your QuickBooks Company File. An Invoice is similar to many other QuickBooks entities in that it has a one-to-many relationship with its Line Item table, InvoiceLineItems. Other such entities are PurchaseOrders, SalesOrders, etc. A single Invoice comprises one or more Line Items in the InvoiceLineItems table, so InvoiceLineItems can contain the data for many Invoices. The technique described in this article can be used with any such table: we create a new Invoice by inserting a new row into a spreadsheet that we link with InvoiceLineItems.

We assume you already know how to connect to QuickBooks using the RSSBus Excel Add-In for QuickBooks. Please see the Getting Started section of the documentation if you are not familiar with connecting to QuickBooks.

Adding the first row to InvoiceLineItems will create the underlying Invoice. It is possible to create an Invoice from the Invoices table, but that requires aggregating all the Line Items into one column, which makes it harder to work with individual elements. We discuss this approach in another article; please refer to the Invoices table documentation for an example. In this article, we'll write up an Invoice by inserting rows of Line Items.

Inserting an Invoice

  • Step 1: Add a connection to QuickBooks with the "Insert" option checked.
  • Step 2: Retrieve data from the InvoiceLineItems table. Since we are only interested in creating a linked spreadsheet and not in the data of InvoiceLineItems, we can speed up the process by limiting the rows that are retrieved. You can do so by setting the maximum rows to 1 in the query wizard.
  • Step 3: After the new sheet selected from InvoiceLineItems is available, you can start adding Line Items to it. To insert a Line Item, just add information in a row directly below the last result. You must specify CustomerName and some Line Item information such as ItemName and ItemQuantity.
  • Step 4: 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.
  • Step 5: After inserting the Invoice, a unique string will show up in the InvoiceId column. 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".

Drivers are now available through CData Software!