How to insert Invoice using Excel - VBA
Download Sample
In this example, We will show you that How to insert Invoice in QuickBooks using the data available in Excel spreadsheet.
We have an Excel spreadsheet which contains Invoice related information. Excel spreadsheet have a CustomerRefFullName, RefNumber, TxnDate, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefFullName, FQSaveToCache information.
Using this information we will create Invoice in QuickBooks which has 4 line items.

Now we will add button & we will write code for button click event. On button click, it inserts Invoice in QuickBooks.
You can add button from Developer tab in MS Excel. If the Developer tab is not available, display it.
Display the Developer tab Click the File tab, click Options, and then click the Customize Ribbon category. In the Main Tabs list, select the Developer check box, and then click OK.
On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to ADD Invoice. Double click on the command button to write code.

Write below code for Inserting Invoice in QuickBooks from Excel spreadsheet.
Private Sub CommandButton1_Click()
Dim sh As Worksheet Dim rw As Range Dim RowCount As Integer Dim CustomerRefFullName As String Dim RefNumber As String Dim TxnDate As Date Dim InvoiceLineItemRefFullName As String Dim InvoiceLineDesc As String Dim InvoiceLineRate As Single Dim InvoiceLineQuantity As Single Dim InvoiceLineSalesTaxCodeRefFullName As String Dim FQSaveToCache As Boolean Dim oConnection As New ADODB.Connection Dim sConnectString Dim sSQL As String Dim rs Dim sMsg RowCount = 0 sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;" sSQL = "SELECT * FROM InvoiceLine" Set rs = New ADODB.Recordset oConnection.Open (sConnectString) rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic Set sh = ActiveSheet For Each rw In sh.Rows
If (RowCount > 0) Then CustomerRefFullName = sh.Cells(rw.Row, 1).Value If (CustomerRefFullName = "") Then Exit For End If
RefNumber = sh.Cells(rw.Row, 2).Value TxnDate = sh.Cells(rw.Row, 3).Value InvoiceLineItemRefFullName = sh.Cells(rw.Row, 4).Value InvoiceLineDesc = sh.Cells(rw.Row, 5).Value InvoiceLineRate = sh.Cells(rw.Row, 6).Value InvoiceLineQuantity = sh.Cells(rw.Row, 7).Value InvoiceLineSalesTaxCodeRefFullName = sh.Cells(rw.Row, 8).Value FQSaveToCache = sh.Cells(rw.Row, 9).Value rs.AddNew rs("CustomerRefFullName") = CustomerRefFullName rs("RefNumber") = RefNumber rs("TxnDate") = TxnDate rs("InvoiceLineItemRefFullName") = InvoiceLineItemRefFullName rs("InvoiceLineDesc") = InvoiceLineDesc rs("InvoiceLineRate") = InvoiceLineRate rs("InvoiceLineQuantity") = InvoiceLineQuantity rs("InvoiceLineSalesTaxCodeRefFullName") = InvoiceLineSalesTaxCodeRefFullName rs("FQSaveToCache") = FQSaveToCache rs.Update End If
RowCount = RowCount + 1 Next rw sMsg = sMsg & "Invoice Added!!!" MsgBox sMsg
rs.Close oConnection.Close End Sub



On clicking on ADD Invoice button, Invoice will be added to QuickBooks.

Invoice Added.

You can see the same in QuickBooks Invoice is added.

|