Knowledgebase
[QODBC-ALL] How to export QuickBooks Invoices into MS Excel Report format
Posted by Jack - QODBC Support on 21 June 2017 06:39 AM

How to export QuickBooks Invoices into MS Excel Report format

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into MS Excel Report.

The attached invoice is similar to what we need to export to MS Excel. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Excel format using QODBC.

You can do it through VBA in MS Excel.

The first step is to download Invoice Template in MS Excel.

You can download the template by clicking "New," & you can find the template under "Office.com Templates." Click on "Invoices" to see the list of Invoice templates.

You can download the template by selecting the template as per your requirement & click on the "Download" button to download the template. I have downloaded the "Service invoice (Garamond Gray Design)" template in this example.

The invoice Template is available with sample data. You can modify the template as per your requirement.

I have made changes to the template. I have changed some existing captions & added ITEM & ITEM DESCRIPTION. I have removed company information, logo, CUSTOMER ID, SALESPERSON, JOB, and LINE TOTAL.

We will fill out this information dynamically by VBA code. We will add a button asking for the RefNumber of Invoice by entering RefNumber VBA code will fetch Invoice detail for particular RefNumber & fill details in Invoice template.

Now we will add a button & we will write code for the button click event. On button click, it fetches the Invoice from QuickBooks & fills it in the template.

You can add a button from the Developer tab in MS Excel. If the Developer tab is unavailable, follow the steps below to display it.

Click the "File" tab, and click "Options."

Click the "Customize Ribbon" category. In the "Main" Tabs list, select the "Developer" checkbox, and then click "OK."

On the "Developer" tab, in the Controls group, click "Insert," and then under "ActiveX Controls," click "Command Button."

A Command Button is added. I have renamed it to "Enter Invoice #." Double-click on the command button to write code.

Write the below code for export QuickBooks Invoices into MS Excel Report format.

Application Source Code:

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A18:A38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B18:B38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C18:C38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D18:D38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("E18:E38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F18:F38").ClearContents

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
Dim InvNo As String

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

InvNo = InputBox("Enter RefNumber:", "InvNo")
If (InvNo = "") Then
    Exit Sub
End If
sSQL = "SELECT TxnID,CustomerRefFullName,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,ShipAddressAddr1,ShipAddressAddr2,ShipAddressCity,ShipAddressState,ShipAddressPostalCode,TermsRefFullName,DueDate,Subtotal,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount FROM InvoiceLine where RefNumber = '" & FnRef(InvNo) & "'"


Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.activeconnection = oConnection
oRecordset.Open sSQL

ActiveWorkbook.Sheets("ServiceInvoice").Activate
ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").Value = oRecordset.Fields("TxnDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").Value = oRecordset.Fields("RefNumber")
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").Value = oRecordset.Fields("CustomerRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").Value = oRecordset.Fields("BillAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").Value = oRecordset.Fields("BillAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").Value = oRecordset.Fields("BillAddressCity") + " " + oRecordset.Fields("BillAddressState") + " " + oRecordset.Fields("BillAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").Value = oRecordset.Fields("ShipAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").Value = oRecordset.Fields("ShipAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").Value = oRecordset.Fields("ShipAddressCity") + " " + oRecordset.Fields("ShipAddressState") + " " + oRecordset.Fields("ShipAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").Value = oRecordset.Fields("TermsRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").Value = oRecordset.Fields("DueDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").Value = oRecordset.Fields("SubTotal")

Dim count As Integer
count = 0
Do While Not oRecordset.EOF
    ActiveWorkbook.Sheets("ServiceInvoice").Range("B" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineItemRefFullName")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("C" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineDesc")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("D" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineQuantity")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("E" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineRate")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("F" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineAmount")
    count = count + 1
    oRecordset.MoveNext
Loop

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

End Sub

 

We need to add the below function for passing the RefNumber parameter value in the query.

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function


Function FnRef(MyRef As String) As String

MyRef = Nz(MyRef, "")
FnRef = MyRef
End Function

 

You can check functionality by clicking on the "Enter Invoice #" button.

MS Excel will prompt for RefNumber, as shown in the below sample. Enter a RefNumber value and click "OK."

The report is available for particular RefNumber.

You can print the report by clicking "Print." The Invoices in MS Excel report format are shown below.

Please click here to download Sample Report.

(0 vote(s))
Helpful
Not helpful
Comments (2)
Ranier Soliman
20 February 2018 08:48 AM
I dont get where to place the functionality


"We need to add below function for passing RefNumber parameter value in the query."

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function


Function FnRef(MyRef As String) As String

MyRef = Nz(MyRef, "")
FnRef = MyRef
End Function
Jack
20 February 2018 01:20 PM
Hi Ranier,

This function will get RefNumber input from the Excel file & pass it to the query for fetching the Invoice details for passed RefNumber.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).