Knowledgebase: QODBC
[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 template under "Office.com Templates". Click on "Invoices" to see the list of Invoice templates.

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

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

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

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

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

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

Click the "File" tab, 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 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 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

 

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

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

The report available for particular RefNumber.

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

Please click here for downloading 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).