IN THIS CHAPTER
An Example QuickBooks SDK Application
Arranging for the Dynamic Link Library
Exploring the CustomerQuery Request Code
Exploring the CustomerQuery Response Code
A software development kit, whether it's for QuickBooks, Microsoft Office, Adobe Acrobat, or any of a long list of popular applications, is a set of tools you can use to extend the application's reach. The software development kit (SDK) for QuickBooks is comparatively easy to use, but learning how to use it is another matter. I'll try to improve that situation in this chapter.
In the previous paragraph, I said that an SDK can help extend an application's reach. That's an ambiguous statement, but the ambiguity is deliberate: It's very difficult to define crisply just what the SDK can do for you. The best I can do is to cite some examples of how I've used it:
To calculate inventory valuation and the cost of goods sold according to FIFO (first-in, first-out) order, and to automate the use of a different method of calculating average cost than QuickBooks uses.
To create donor acknowledgment letters for nonprofits (which Intuit wrongly implies that its QuickBooks nonprofit edition can do).
To enable the batch entry of sales receipts, so the user need not fill out a separate sales receipt for each sales transaction.
To create bills of materials and where-used lists for assemblies.
I use the SDK for various other purposes. The point is that if you want to do something with the information that's in QuickBooks, and if it's difficult to get the data out using reports, then the SDK is usually a good alternative. Even if exporting a report is a feasible solution, using the SDK can work better than exporting reports.
There are downsides to using the SDK, and I'll explain those as well. When you have finished reading this chapter you will have a basic grounding in how to use the SDK and you'll be better placed to decide whether using it can help you get more out of QuickBooks.
Here's an example of the sort of thing you can do with the SDK. It's conceptually straightforward and solves a problem that quite a few new users encounter. Even though the solution is straightforward, I'll make some simplifying assumptions — it's an example, after all.
Suppose that when you were just starting out with QuickBooks, you entered the names of several hundred customers in the Customer Center. Instead of entering a value in the First Name edit box and another in the Last Name edit box, you entered each customer's full name in the Full Name edit box. Now, several months later, you'd like to send out some letters using only the customer's first name in the salutation; for example, Dear Judy instead of Dear Judy Reed.
Therefore, you'd like to parse the customer name and put the first name into QuickBooks' First Name field and the last name into the Last Name field. You'd like to automate the process, not because you expect to have to do it again in the future, but because you have hundreds of customer names to parse.
The QuickBooks SDK provides generic programming code that you can easily adapt to meet your particular requirement. The next section steps you through one possible solution.
Note
I structured this example as I did to keep things simple. Here's a similar scenario, just slightly more complicated: Suppose you wanted customers to appear in the Customer Center and in reports in alphabetical order by last name. If so, you would want the customer name to appear as "Last Name, First Name." The QuickBooks user interface won't let you manage that by typing the first and last name into their respective edit boxes. The most efficient solution is to use a minor variation on the approach outlined here: Type the customer name as you want it to appear, and then use the SDK to tease out the first name and last name and put them into their respective fields.
Before you can edit QuickBooks data using the SDK you have to get at the data. There are two phases to extracting data from QuickBooks via the SDK: establishing a connection to QuickBooks and then presenting a query to QuickBooks through that connection.
After presenting the query and acquiring its results, you need to do the required editing and write the edited data back to QuickBooks. The next section explains the connection and query processes; then I cover the editing and write-back processes.
You don't need to be a programmer to use the SDK, but you do need to be able to modify the existing program code to get it to solve your particular problem. You'll see how to do that here. You'll also see how the code supplied by the SDK manages your connection to QuickBooks.
To edit any QuickBooks record using the SDK, you need at a minimum the record's values in three fields, and the code presented in this section shows you how to go about retrieving that data. The three fields are as follows:
Each record in QuickBooks has a unique ID that unequivocally identifies the record. You cannot access the ID through QuickBooks' user interface; for example, the Customer Center does not display customer IDs, and neither does any report. If you want to modify Judy Reed's record in the Customer Center, you can easily do so by selecting her record. But if you want to use the SDK's code to edit many records at once, you need access to the customers' IDs.
Every time you alter a customer record, QuickBooks supplies the record with a new edit sequence number, similar to an ID number. (QuickBooks does so regardless of whether you carry out the edit via the user interface or via the SDK.) The customer ID identifies the particular customer, and the edit sequence number identifies the current version of the customer's record.
QuickBooks is a multi-user application, so it's possible, if unlikely, that someone else might want to edit Judy Reed's customer record at nearly the same time you do. So, to keep things straight, QuickBooks requires that you supply the edit sequence number that it assigned her record the last time the record was edited. To supply that edit sequence number, you need first to have retrieved it.
If you supply the correct edit sequence number along with the new values for Judy Reed, QuickBooks knows that you're editing the most recently saved version of her record. If you supply the wrong number, that could be because someone else edited the record between the time that you retrieved it and the time that you try your update. It's important that you work with the most recent version of the customer's data, so QuickBooks won't accept your update if you don't supply the current edit sequence number. Once you have the customer IDs and edit sequence numbers, QuickBooks lets you modify the other information in the customer record via the SDK.
In this case, you want to access the Customer Name. You need it so that you can split it into a first name and a last name. Later, when you're ready to write your data back to QuickBooks, you want the code to write those values into the customer record's FirstName and the LastName fields.
The code described in this section is in the Basic programming language. You can run it in any application that supports the use of Basic, including Microsoft Excel and Microsoft Word.
It's important to recognize that the code presented here is taken directly from the QuickBooks SDK. I have modified it so that it focuses on the essentials, as follows:
The code as supplied by the SDK contains certain tests. For example, it checks to see whether a query that you submit successfully returns any records from the QuickBooks company file. Such tests are helpful, even essential, in a production environment, but in an example they get in the way of the main points and so I have deleted them here.
The code as supplied by the SDK does not use certain helpful features that may be available in the application running the code. I have added a couple of such statements to the code: The statements put the data from QuickBooks into an Excel worksheet, just to make the results clearer. I'll identify those statements for you.
Again, the important thing to remember is that of the 32 statements in this section, I supplied only three, and made minor modifications to four more. The remaining statements all come directly from the SDK. In many cases you can do the same thing I've done: Copy the code that's supplied by the SDK and then tweak it a little to suit your own purposes.
Before running the code you should be sure you have QuickBooks running, with the company file you're interested in open. You should also make sure that QuickBooks is not displaying a dialog box that is waiting for input from you.
Note
It never hurts to back up your QuickBooks company files, and using the SDK's code provides you another opportunity to do so. I have never experienced a situation in which running the SDK code corrupted or otherwise damaged the data in a company file; in fact, I have used the SDK to correct damage that was caused by misusing the user interface. Nevertheless, you should consider backing up your company file before running code that you develop via the SDK, and I strongly recommend that you do so before running any code that's intended to modify data in your company file.
The first six statements in this little program establish a subroutine and declare that four variables exist. The subroutine is identified by the keyword Sub and the variable declarations begin with the keyword Dim (short for Dimension, a legacy term that's been around at least since the earliest days of Fortran).
Sub DoCustomerQueryRq() Dim sessionManager As New QBSessionManager Dim requestMsgSet As ImsgSetRequest Dim customerQuery As ICustomerQuery Dim responseMsgSet As ImsgSetResponse
The four Dim statements declare that four variables exist. The keyword As indicates that each variable is of the type that follows the As: ImsgSetRequest, ICustomerQuery, and so on. These types are defined in QuickBooks. You don't need to know what they are, what they represent, or what properties they have. It doesn't hurt to know, of course, and if you're curious you can find out more about them by consulting the SDK's on-screen documentation — more about that in later sections of this chapter.
The next two statements establish a message for QuickBooks that will request some data; the first statement includes information about the country version of QuickBooks that the code assumes is in use, as well as the release version information. The second statement defines the request message as one that asks for information about customers.
Set requestMsgSet = sessionManager.CreateMsgSetRequest("US",6,0)
Set customerQuery = requestMsgSet.AppendCustomerQueryRqThen the session is opened so that the request for data can be passed to QuickBooks.
sessionManager.OpenConnection "BAQB", "Parse Customer Name"
You have plenty of discretion in specifying the arguments in this statement. The first argument is an application identifier, and in this example it's specified as "BAQB" but could be your name, or initials, or even an empty pair of quotation marks (""). You do need to provide some text value for the second argument, which here is "Parse Customer Name" and is saved as a Company Preference in QuickBooks; Choose Edit
When the SessionManager.OpenConnection statement is executed, QuickBooks responds by displaying the QuickBooks – Application Certificate dialog box shown in Figure 11.1.
Select one of the four options in the Application Certification dialog box. It's probably most convenient to select Yes, Whenever this QuickBooks Company File Is Open. Even if you needed to run this particular example on your company files, you would need to use it only once. But you might want to periodically run other analysis applications that you develop, and you don't want to deal with the dialog box shown in Figure 11.1 every time. Subsequently, you can always modify your choice in QuickBooks' Integrated Applications preferences.
The next statement actually starts the session that extracts data from QuickBooks. The empty quotation marks are where you could place the company file's path and name if one weren't already open in QuickBooks. The omDontCare argument specifies that you don't care whether QuickBooks is open in single-user mode or multi-user mode.
sessionManager.BeginSession "", omDontCare
Now the variable responseMsgSet is identified as the place to put the request's results (in this example, the results of performing a customer query). The DoRequests action directs the requestMsgSet to QuickBooks, which responds by putting the requested data into the response message set.
Set responseMsgSet = sessionManager.DoRequests(requestMsgSet)
When QuickBooks has responded to the query (which for this example is usually a matter of seconds at most), the code ends its session with QuickBooks and closes the connection.
sessionManager.EndSession sessionManager.CloseConnection
Figure 11.1. You'll have a chance to confirm your choices after you click Continue in the Application Certificate dialog box.
Last, the response from QuickBooks (contained in the responseMsgSet variable) is sent to another subroutine that actually separates a customer's first and last names. That subroutine is shown in the next section. Then, the current subroutine ends.
ParseCustomerQueryRs responseMsgSet End Sub
Look back to the seventh statement in this subroutine, where the customer query is set to AppendCustomerQueryRq. The latter is an existing query available to you via the SDK and its connection to QuickBooks. As it stands, it makes reference to all customers and to all customer fields — name, billing address, shipping address, credit card information, and so on. (For purposes other than dealing with customer records, you use different messages; there are several for items, for accounts, for vendors, for transactions, and so on.)
When you start writing your own applications for use with the SDK, you can often trim down the volume of data that's returned from QuickBooks, both the number of fields and, via filters, the number of records. For now, the important point to recognize is that you don't need to assemble a query from scratch. You just need to know that one exists and what its name is — and you can get that from the SDK's Onscreen Reference.
I have adapted, slightly, the SDK code shown in this section — and I'll show you where I've done so — to put the query results into an Excel worksheet where you can see them. So doing isn't the most efficient way of accomplishing the task, but it helps to see intermediate results as you're getting comfortable with what's going on.
The subroutine covered in this section, ParseCustomerQueryRs, is also available from the SDK. As was the case in the previous section, the code has been modified to focus on the actual work; I have removed tests that are included in the SDK-supplied code and that I'd want to use in a production situation. I have also removed a For ... Next loop that would execute once for each request the code sends to QuickBooks. In this example there's only one request, so I removed the loop.
The Sub statement establishes the subroutine and specifies responseMsgSet as the subroutine's only argument:
Public Sub ParseCustomerQueryRs(responseMsgSet As IMsgSetResponse)
The sequence of events, starting with the code shown in the previous section, has been:
Submit a query to QuickBooks.
Accept a response from QuickBooks. The response in this example contains customer records with each customer's data in the various customer fields.
The subroutine named
ParseCustomerQueryRshas been called, and the response from QuickBooks, namedresponseMsgSet, has been passed to the subroutine as part of the call.
Now the ParseCustomerQueryRs subroutine writes the customer records and three of the fields in the response to an Excel worksheet.
Six variables are declared. The code you get from the QuickBooks SDK declares all but one, RowNum. I have declared RowNum so that the code will know which row in the worksheet to use when it writes a particular customer's data.
Dim RowNum As Integer, j As Integer Dim responseList As IResponseList Dim response As IResponse Dim customerRetList As ICustomerRetList Dim customerRet As IcustomerRet
Three variables that are needed to obtain customer-by-customer data are set, and the first row that will contain customer data is assigned the value 2:
Set responseList = responseMsgSet.responseList Set response = responseList.GetAt(i) Set customerRetList = response.Detail RowNum = 2
The SDK supplies the three Set statements; I supplied the statement that assigns 2 as RowNum's starting value.
A loop is started. It runs once for every record returned by the query to the customerRetList variable. Notice that the index to the records starts at zero: lists such as the returned customer data are zero-based in the SDK.
For j = 0 To customerRetList.Count – 1
The elements in the customerRet variable are set equal to all the field values for the current, jth customer in customerRetList. The customerRet variable represents a specific customer, whereas customerRetList contains all customers returned from QuickBooks.
Set customerRet = customerRetList.GetAt(j)
Now the three fields needed to complete the task — the customer ID, the record's edit sequence value, and the customer name — are written to the active worksheet. The ID goes in the first column, the edit sequence in the second column, and the customer name in the third column. All three values are written to the row specified by the current value of RowNum:
ActiveSheet.Cells(RowNum, 1) = customerRet.ListID.GetValue ActiveSheet.Cells(RowNum, 2) = customerRet.EditSequence.GetValue ActiveSheet.Cells(RowNum, 3) = customerRet.Name.GetValue
After the three values are written to the worksheet, the value of RowNum is incremented and the loop proceeds to the next customer. When the final customer record has been processed, the subroutine ends.
RowNum = RowNum + 1 Next j End Sub
The result of running these two subroutines appears in Figure 11.2, where only four customers are shown.
All that's needed now is to separate the first from the last names, and write them back to QuickBooks. That process is explained in the next section.
As you'll see in the section "Using the Onscreen Reference," you get the necessary code to accomplish a task such as modifying customer data by finding the appropriate message in a dropdown supplied by the SDK. You have to know what you're looking for, and that knowledge comes only with experience using the SDK. Even the first time through, though, you'd probably find the one covered here, because it's called CustomerMod.
Figure 11.2. The RowNum variable in the subroutine was initialized to 2 to leave room for the column headers.
To edit the customer data — in this case, to supply each customer record with a value for the First Name and the Last Name fields — you don't use the CustomerQuery message that you used to retrieve the existing data from QuickBooks. You've already retrieved the data, and now it's time to edit it, so you use a different message, the CustomerMod message. By asking for that message you get the code shown in this section. As before, I've done some editing to what the SDK provides, and I'll point out any code that I've changed or added.
As before, the subroutine begins with several declarations, each provided by the SDK:
Public Sub DoCustomerModRq() Dim sessionManager As New QBSessionManager Dim requestMsgSet As IMsgSetRequest Dim responseMsgSet As IMsgSetResponse Dim customerMod As IcustomerMod
I have omitted the arguments Country, MajorVersion, and MinorVersion from the Sub statement. It's not good programming practice, but for current purposes I supply them later as constants.
I have declared four variables in addition to those declared by the SDK. Three are declared as string (that is, pure text) variables; they are for use in parsing the customer name. The fourth is used to keep track of the code's progress through the data in the worksheet shown in Figure 11.2.
Dim FirstName As String, LastName As String, FullName As String Dim RowNum As Integer
A message set that will contain the edits to the customer records is created. The version of this statement that's provided by the SDK uses the variables Country, MajorVersion, and MinorVersion instead of the constants US, 6, and 0. Solely for this example, it's more efficient to use the constants and so I've done so in the next statement.
Set requestMsgSet = sessionManager.CreateMsgSetRequest("US", 6, 0)Then the code is instructed to continue processing requests even if an error is found in any one of them. (The alternative to roeContinue is roeStop, which causes processing of requests to stop if and when an error is found.)
requestMsgSet.Attributes.OnError = roeContinue
Now a loop is run, in this case four times, from 2 to 5. (I have supplied this loop to show how you can modify many records in QuickBooks by executing a single procedure; the SDK's example code shows you how to modify one record only.) The loop works its way through the four rows in the worksheet that contain customer data; again, refer to Figure 11.2. Each time the loop executes, another modification request is appended to the message set that will later be passed to QuickBooks.
For RowNum = 2 To 5
Set customerMod = requestMsgSet.AppendCustomerModRqThe customer ID and the edit sequence number are picked up from the active worksheet. These values are found in the row identified by the current value of RowNum, and in the first and second columns respectively. I supplied the worksheet locations in the code.
customerMod.ListID.SetValue ActiveSheet.Cells(RowNum, 1) customerMod.EditSequence.SetValue ActiveSheet.Cells(RowNum, 2)
I have supplied the next three statements, which take care of the actual parsing of the customer's full name into separate first and last names. The customer name, consisting of the customer's first name followed by the customer's last name, is read from the active worksheet into the string variable FullName.
FullName = ActiveSheet.Cells(RowNum, 3)
Next, use Visual Basic's InStr function to locate the space between the first name and the last name in FullName. Use the Left function to get the character string that comes before the blank space:
FirstName = Left(FullName, InStr(FullName, " ") - 1)
That is, if FullName were "Judy Reed" then the variable FirstName would resolve to the expression Left("Judy Reed", 5 - 1) or simply "Judy."
Now Use the length of the FirstName to locate the LastName:
LastName= Right(FullName, Len(FullName) - Len(FirstName) - 1)
Use the Len function to get the length of FullName and subtract from that the length of FirstName. Subtract 1 to account for the blank space. The result is the number of characters at the right end of the FullName string that make up the customer's LastName.
Then assign the values in FirstName and LastName to the corresponding fields in the customer modification message set, and go back to the start of the loop to pick up the next customer. Or, if it's the final time through the loop, exit the loop and execute the next statement following the loop.
customerMod.FirstName.SetValue FirstName
customerMod.LastName.SetValue LastName Next RowNum
The CustomerMod message now has the first and last names of each customer, parsed out of the customer's full name. Next, establish a connection to QuickBooks and begin a data exchange session. Send the message set with the customer modifications to QuickBooks. Then end the session, close the connection, and end the subroutine.
Note
What follows has occurred earlier when the Customer Query connected with QuickBooks to retrieve the customer IDs, edit sequence values, and customer names. The difference here is that the code submits values to QuickBooks instead of retrieving them.
sessionManager.OpenConnection "BAQB", "Parse Customer Name" sessionManager.BeginSession "", omDontCare Set responseMsgSet = sessionManager.DoRequests(requestMsgSet) sessionManager.EndSession sessionManager.CloseConnection End Sub
If you actually run this code on your own company file, you'll find that everything prior to the first space in a customer's full name is used as the First Name field for that customer record. Everything following the first blank space is used as the Last Name field for that record.
Because the code as given so far in this chapter is intended solely as an introductory example, it makes a variety of assumptions about the customer data in the company file. This section identifies those assumptions and provides some suggestions as to how you might handle them. Although they are specific to the customer name example, you should be able to generalize them to other, more complex situations.
The code as written assumes that there are only four customers whose names need to be parsed. The retrieval code does not make this assumption; it returns as many records to the worksheet as there are customers in the company file. But the code that parses the names and sends modifications to QuickBooks assumes that the records are in rows 2 through 5 of the worksheet. The code in question looks like this:
For RowNum = 2 To 5 [Code that parses the names and passes the values to the message set] Next RowNum
Here is a more general approach, one that depends only on the data starting in row 2:
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(2, 1).End(xlDown).Row For RowNum = 2 To LastRow [Code that parses the names and passes the values to the message set] Next RowNum
The major difference is that the loop terminates, not after RowNum passes 5, but after RowNum passes the value stored in the variable named LastRow. Here's how LastRow gets its value:
Suppose that your worksheet has some unknown number of records starting in row 2 and that every record has a value in column A. This is the situation in the current example: Every customer record has an ID value in column A, as the retrieval code is written. A moderately experienced Excel user knows that to find the final used row, you select cell A2, press Ctrl+Z. Excel responds by selecting the bottommost of the contiguous cells in column A that contain values. In this example, if cells A1:A100 contain values and cell A101 is empty, Excel responds by selecting cell A100.
This fragment does the same thing programmatically:
ActiveSheet.Cells(2, 1).End(xlDown)
And when you add .Row to it, as follows:
LastRow = ActiveSheet.Cells(2, 1).End(xlDown).Row
the statement returns the row number of that last contiguous cell. By assigning that number to the variable LastRow, you can run the loop knowing that it will stop after it processes the last customer record, and not before.
The code as written assumes that the customer's full name consists of a first name, followed by a blank space, followed by a last name. There are many types of customer names that do not follow this pattern; for example, the presence of a middle name, an honorific such as D r. or a suffix such as S r. throws off the code's logic. If you ran the code on a full name such as "Dr. Benjamin Spock," it would return "Dr." as the first name and "Benjamin Spock" as the last name. James T. Kirk's first name would be "James" and his last name would be "T. Kirk."
There are various ways to handle something like this. One is to ignore the problem. If you had, say, 800 customer names in your company file and estimated that perhaps eight of them would pose this sort of problem, it's easier to run the code and do the corrections by hand after the fact, in the QuickBooks Customer Center.
A different approach is to write code that counts the number of blank spaces in the full name. If there is one blank space only, run the code as given. If there are two blank spaces, put everything to the right of the second blank in the LastName variable, and everything between the blanks in a MiddleName variable. Unfortunately, this approach fails when full names include prefixes such as Ms. and suffixes such as J r.
A third approach, similar to the first given in this section, is to move the statements that parse the name into the subroutine that writes the full name to the worksheet, and parses it into as many sections as the full name contains. Before you run the customer modification code, examine the results of the parsing in the worksheet and correct any errors there. You might allocate one column each for a prefix (which the SDK terms a salutation), first name, middle name, last name, and a suffix. Edit the modification code accordingly, so that you have a section such as this:
For RowNum = 2 To 5
Set customerMod = requestMsgSet.AppendCustomerModRq
customerMod.ListID.SetValue ActiveSheet.Cells(RowNum, 1)
customerMod.EditSequence.SetValue ActiveSheet.Cells(RowNum, 2)
customerMod.Salutation.SetValue ActiveSheet.Cells(RowNum, 3)
customerMod.FirstName.SetValue ActiveSheet.Cells(RowNum, 4)
customerMod.MiddleName.SetValue ActiveSheet.Cells(RowNum, 5)
customerMod.LastName.SetValue ActiveSheet.Cells(RowNum, 6)
customerMod.Suffix.SetValue ActiveSheet.Cells(RowNum, 7)
Next RowNumThe advantage to this approach is that you can visually scan all the results in a worksheet and make any necessary corrections by hand before you run the modification code. Otherwise you have to find errors and make corrections one by one in QuickBooks' Edit Customer dialog box.
This specific advantage is just a single instance of a much more general benefit to using the SDK, as follows:
Tip
It can be much more efficient to add or edit QuickBooks records in the aggregate in an Excel worksheet than one by one in a QuickBooks dialog box.
It can be more efficient to edit In a worksheet for various reasons. For example:
In a worksheet you have access to many editing functions, such as copy and paste, search and replace, and sorting records, that can make the process of adding or modifying information much faster. In a QuickBooks dialog box, you can edit only one record at a time.
It often happens that you want to edit a subset of records. You normally cannot pick a subset of records, whether by means of a filter or a sort, to edit using the QuickBooks interface. That means you have to step through the records one by one, looking for records that you want to edit. By contrast, Excel has two general types of filters, AutoFilter and Advanced Filter, that ease the selection of records that you want to edit en masse.
There are some types of summaries and details that you simply cannot get from reports, whether designed by QuickBooks or customized by the user. An example is the relationship between an invoice and the payment of the invoice. The QuickBooks user interface doesn't enable you to view them together, which you often want to do in the event of, for example, partial payments. Linking the invoices and payments in a worksheet is a good solution.
To keep the focus on the main task of parsing customer names and updating QuickBooks with the result, the example code given so far has omitted certain tests and recovery paths. While Intuit does not represent the code that accompanies the SDK as the most efficient, or as exemplary of best practices, it does include some useful protections. I cover a few of them here so that you'll recognize them when you use the SDK code to create your solutions based on what the SDK provides.
One sequence that you'll find in virtually all the Visual Basic code that comes with the SDK concerns arranging for an orderly exit if something unexpected happens. By "unexpected" I mean something such as a runtime error — a situation that prevents the code from continuing to run to a normal completion. Such a situation might be, for example, a customer with only one name. Here's a typical setup:
Public Sub DoCustomerQueryRq(country As String, majorVersion As _
Integer, minorVersion As Integer)You should tell the code what to do next if an error occurs, and you can do so with an On Error statement. In this case, the code will branch to a statement labeled Errs.
On Error GoTo Errs
Declare and initialize two Boolean variables: bSessionBegun, which stores information about whether a QuickBooks session has begun, and bConnctionOpen, which stores information about whether a connection to QuickBooks is open. (Boolean refers to logical constructs; in this case, True and False.) At the outset these variables are set to False.
Dim bSessionBegun As Boolean bSessionBegun = False Dim bConnectionOpen As Boolean bConnectionOpen = False
When the connection is opened, and when the session is begun, set the two Boolean variables to True.
sessionManager.OpenConnection "BAQB", "Parse Customer Name" bConnectionOpen = True sessionManager.BeginSession "", omDontCare bSessionBegun = True [Code that manages the exchange of information with QuickBooks]
When all goes as expected, the information is exchanged with QuickBooks and program control flows normally, the session is ended and the connection is closed. The Boolean variables are set to False, and the subroutine is exited. Exiting the subroutine stops the processing, or, if another subroutine called this one, control returns to the calling subroutine.
sessionManager.EndSession
bSessionBegun = False
sessionManager.CloseConnection
bConnectionOpen = False
Exit SubHowever, suppose that an error occurred during the exchange of information with QuickBooks or during the processing of the data. According to the On Error statement at the start of this subroutine, control comes here when an error occurs:
Errs:
The statements following the Errs label execute next. Notice that if no error occurred, the Exit Sub statement would divert control away from these statements; the subroutine is exited before control comes to Errs. Otherwise, the first statement after the Errs label displays a message box with information about the error:
MsgBox "HRESULT = " & Err.Number & " (" & Hex(Err.Number) & ") " _
& vbCrLf & vbCrLf & Err.Description, vbOKOnly, "Error"Then, following the Errs label in the code that you get from the SDK, there is a statement that normally causes its own runtime error:
SampleCodeForm.ErrorMsg.Text = Err.Description
Unless you happen to have a user form named SampleCodeForm in your Excel workbook, the code will stop with a runtime error. Even if you have a form with that name, nothing will happen because the code does not cause the form to be shown. Because I don't want to show a user form in the event of an error, I routinely delete this statement from code that I obtain from the SDK.
With that statement out of the way, an orderly termination can take place. The QuickBooks session is terminated and the connection is closed. Notice that the EndSession command executes only if a session has begun, and the CloseConnection command executes only if a connection is open.
If (bSessionBegun) Then
sessionManager.EndSession
End If
If (bConnectionOpen) Then
sessionManager.CloseConnection
End If
End SubThere's one particular tool that you absolutely must have if you're to use the sort of programming explained in the previous section. That tool is a dynamic link library, or DLL, which is provided for free by Intuit. This section explains the rationale for the DLL and how you can obtain it along with the rest of the SDK.
Although this section is about a file called a dynamic link library, or DLL, it helps to start with a brief history lesson. The Basic programming language was developed in the 1960s. It was originally intended as an introductory language but its comparative simplicity brought it wide popularity, and support for it was included with the early PC operating systems.
Microsoft extended the Basic language in the 1990s by adding support for graphic elements such as dialog boxes and controls such as command buttons and tab strips. Because programmers could now incorporate these elements and display them on a computer screen, the language was known as Visual Basic.
Versions of Visual Basic, called Visual Basic for Applications, or VBA, were included in Microsoft Office applications such as Excel and Word beginning in the mid-1990s. These versions are "for Applications" because they include direct support for objects that exist in the application. For example, in Excel VBA you can make reference to a particular worksheet and cell with syntax like this:
Workbooks("Financials").Worksheets("Balance Sheet").Cells(1,1)="2010"Pure Visual Basic has no idea what a workbook is, or a worksheet or a cell, but VBA for Excel knows exactly what they are. The reason is that the Excel object model — a sort of library that defines objects like worksheets, rows, columns, and other characteristics of Excel workbooks — is "exposed" to VBA. With the definitions of those objects available, VBA can manipulate them. So Visual Basic for Applications is a distinct version of Visual Basic that knows about objects in a Microsoft Office application.
You don't have to do anything special to make the library of Excel objects available to code written in Excel VBA. That's taken care of for you. But Microsoft did not also expose the QuickBooks object model to VBA.
Suppose you want to use Excel VBA to manipulate QuickBooks objects. That's often an effective technique because Excel isn't a great accounting package any more than QuickBooks is a great financial analysis package. You need to arrange to expose the QuickBooks object model to Visual Basic for Applications. And the QuickBooks SDK has a way for you to do that. The steps are somewhat tedious, but once through them is enough for any given computer.
The DLL is one of several files in the SDK that might be installed on your computer; the particular one that gets installed depends primarily on the operating system you're running. Once the DLL has been installed, all you need to do is ensure that any code based on the SDK knows that the DLL is available. So, there are three basic steps to making these arrangements: Downloading and running the installer, becoming familiar with the Onscreen Reference guide, and referencing the DLL. These steps may sound intimidating, but they're really not, as the next sections show.
The SDK is available from Intuit for free. The license agreement is not unusually restrictive, and it tells you that you're not permitted to take the actions that most SDKs forbid: reverse-engineering the product, for example.
I recommend that you get Version 6 of the SDK. At the time this book was written, both Version 7 and Version 8 were available, but I do not recommend them. These versions do have some bells and whistles that are not available in Version 6, such as sample code written in C# and support for multiple currencies in QuickBooks 2009.
However, the Basic code samples in Versions 7 and 8 are written per VB.Net conventions, and many statements are not recognized by VBA. I have been using Versions 5 and 6 of the SDK since 2006 and cannot say that I have missed any of the enhancements in Versions 7 and 8. (Although I personally prefer C# to Basic, it's not directly available in Excel.) If you regard the Version 7 or 8 enhancements as essential to implementing whatever you might have in mind, and if you want to run your application in VBA, be aware that you'll have to program your way around the example code provided in Versions 7 and 8.
To download the SDK, point your Web browser to www.developer.intuit.com. Before you can download an SDK, you need to join the Intuit Developer Network. Membership is free, and although I've been enrolled for several years I have never received an unsolicited e-mail from them — or if I did, I've forgotten it and they complied with my opt-out request.
Once you've joined the Intuit Developer Network, navigate to the site's QuickBooks SDK download page. Don't be misled by references to Version 8 (or even later); earlier versions of the SDK are available on the same page. Towards the bottom of the QuickBooks SDK download page you'll see several links to Version 6, Version 5, and so on. Click on Version 6 and follow the instructions to download the installer to your computer.
Once the installer has downloaded, double-click it to execute the installation. You may be required to close certain applications, or to accept a reboot after the installation has concluded. There's no special reason to retain the downloaded installer after it's finished its work, so you can delete it.
The installation process puts a new item in your Start menu: QuickBooks SDK 6.0. If you select that item, you will find that it subsumes a number of documents and folders. Beyond all question the most useful is the Onscreen Reference; later sections of this chapter explore what you can find there.
Other than the Onscreen Reference, the other critically important file is named qbfc6.dll. You'll never open it directly, but you will need to make an application such as Microsoft Excel aware of its existence. A Windows search is the surest way to locate it, but after installing the SDK you should find it in C:Program FilesCommonIntuitQuickBooks.
One other document of interest, also available from QuickBooks SDK 6.0 in the Start menu, is found in the Documentation – PDF folder, and is named QBSDK Programmer's Guide. You'll need the free Adobe Reader to open it (if you don't have it already, you can get it at www.adobe.com/reader). The Programmer's Guide is about 600 pages long, and I find it helpful from time to time as a detailed reference when I need fuller documentation than is available in the Onscreen Reference.
The Onscreen Reference (which the SDK refers to as the OSR; the world of alphabet soup is too much with us) is an HTML document, found under QuickBooks SDK 6.0 in your Start menu. I've bookmarked it in my browser so I can get to it quickly. It has its drawbacks, but I have found it an excellent resource for learning about the SDK and also for getting help when I get stuck.
You use the Onscreen Reference when you want to know what kinds of queries you can direct at QuickBooks, what fields are involved with those queries, and how to put together code that carries out a query. When you open the Onscreen Reference for Version 6 of the SDK, you see the screen shown in Figure 11.3. (Of course, you won't see the contents of the Select Message dropdown until you click it.)
The Select Message dropdown contains a list of roughly 200 messages to select from. Suppose you choose CustomerQuery, which is one of the two queries explained in this chapter (the other is CustomerMod; the OSR generally uses the term query for code that returns data to you, and the term mod for code that modifies data in QuickBooks). Choosing CustomerQuery displays the window shown in Figure 11.4 in your browser.
The term "message" is a generic one and it subsumes different types of communications with QuickBooks. These types include the following.
As the SDK uses the term query, it means a message that retrieves data from QuickBooks. So the CustomerQuery message retrieves information about customers, the InvoiceQuery message retrieves information about invoices, and the ItemQuery retrieves information about items. A query message does not add or modify information in the QuickBooks company file, but it may be necessary to run a query to prepare for modifying data in the file.
Figure 11.4. The window shown is specific to a request. Different elements appear for a response, which is shown in Figure 11.7.
Report messages duplicate information that you can get from built-in QuickBooks reports. For example, the AgingReportQuery returns information that duplicates one of the aging reports. By setting a value in the message, you can arrange for an aging report on accounts receivable (either summary or detail), accounts payable (either summary or detail), and collections.
Most QuickBooks reports either categorize individual transactions (detail reports) or roll transactions into categories (summary reports). You could run a query message that provides the transaction information that you could subsequently process; the result might duplicate information that you get from a report. It might therefore seem as though it's not necessary to deal with report messages.
However, there are at least two general benefits to running a report message:
You don't have to do the processing that categorizes transactions, or that totals their dollar amounts.
You avoid errors in processing that lead to inconsistencies between your application and the output of the built-in report.
An add message, as you might expect, adds records to the QuickBooks company file. You could use the CustomerAdd message to add new customer records to the company file. When you do so, you can simultaneously specify the values that belong to a customer record: the phone number, the street address, the contact name, and so on.
Add messages can be particularly useful when you are establishing a new company file using data from another application. If you can sweep members of a list such as customers, or transactions such as invoices, into the company file in one batch, you can save a huge amount of time and avoid typographical errors, compared to entering the data manually in the QuickBooks user interface.
You use a modification message to edit information in a QuickBooks record (a customer, item, vendor, transaction, etc.) that already exists in the file. Suppose the phone company added an overlay area code in your city. You would probably have to change the area code that you now have assigned to many of your customers. It might well be much more efficient to change all those area codes in an Excel worksheet and then update your company file all at once using a CustomerMod message.
Requests and responses are not different types of messages, but rather two parts of a message. Figure 11.4 shows that in the OSR you can switch back and forth between information in the Request part and the Response part by clicking on the part you want to see. Different elements are shown depending on which part, request or response, you've selected. When it comes time to view the associated sample code, the code that's returned depends on whether a request or a response is currently selected.
The request portion functions to define a query, such as which customers to return, a range of dates for invoices, or a total balance filter to return the names of vendors for whom the balance is larger or smaller than a given amount. If the message is an Add or a Modification, the request portion supplies the values for a record's fields, such as a customer's billing address, an inventory item's description, or the active status of an account. For a report message, the request portion roughly corresponds to the options you set when you modify a report in the QuickBooks user interface.
A query's response carries the values of the fields that the request specified, for the records it specified. You'll almost always need to modify the code associated with a query response so that your application knows what to do with the data that's been retrieved. The same is true of reports, but it can be much trickier to modify a report's response code: You'll find that you're dealing with indeterminate rows and columns instead of defined field names.
The response portion of an Add or Modify request is almost exclusively a duplication of the information supplied in the request. As such, you can use the response as an audit trail, to verify that the changes or additions you called for in the request actually took effect. Among the additional information are fields that report the date and time the edit took place, the date and time the record (the customer, the item, the vendor, and so on) was originally created in the company file, and the current edit sequence value (see the "Establishing the connection and query" section near the beginning of this chapter).
The Tag column label in the OSR is due to the SDK's support for XML, which uses the term tag to mean a type of identifier. In the context of the OSR, the Tag column calls out the elements whose values you can set to control the type and the volume of the information in your request. This section provides a brief overview of each element. Later in this chapter you'll find examples of the way you use each element in your code.
In Figure 11.4, the columns to the right of the Tag column provide information on each element listed, as follows:
This column shows the type of data used by the element. The types are almost all specific to QuickBooks (as opposed to, for example, Integer). I have found little use for this information.
These columns show the maximum number of characters a string element can contain, or the permissible range of a numeric value, when you're addressing either the desktop or online edition of QuickBooks. I have found little use for this information.
This column shows QuickBooks editions in which the element is not implemented. For example, Figure 11.4 shows that using the SDK you cannot use the metaData element with the Online Edition of QuickBooks (QBOE). The Implementation column may also show which version of the SDK introduced the element. So metaData was introduced in version 4.0 and iterator was introduced in version 5.0.
A Y in this column indicates that the element is required, subject to the context. For example, Figure 11.4 shows that MatchCriterion, the 14th element, is required: there's a Y in the Required column. But it's required only if you choose to set the NameFilter, and that's not required. If you do set the NameFilter, to return only a particular Name from QuickBooks, you must set the MatchCriterion so that the Name starts with, contains, or ends with a particular string. Otherwise, if you're not using the NameFilter, MatchCriterion is not required.
Some elements listed in the OSR appear in many messages. For example, Figure 11.4 shows metaData as an element. That element, metaData, can be useful in various queries and appears in the request section of most of them. Other elements are specific to a particular message. This section provides an overview of the CustomerQuery elements. Elements such as metaData function identically in other messages. Elements that are specific to CustomerQuery are often analogous to similar elements in other messages (record filters, for example), You can often infer their function in other messages from their function in the CustomerQuery message.
Tip
A convenient feature of the OSR is that you can click on the name of an element and find brief explanatory information in the window's bottom frame.
metaData returns information about the number of records a query's response contains. I sometimes use it to display a progress indicator when I expect there are many records to retrieve. The default value, mdNoMetaData, returns no count of records. Unless you want to get a record count, you can leave the statement in place or delete it entirely. The code as it appears in the queries looks like this in the CustomerQuery message:
customerQuery.metaData.SetValue mdNoMetaData
and like this in the TransactionQuery message:
transactionQuery.metaData.SetValue mdNoMetaData
Notice that the code as written, which conforms to the SDK's syntax, does not use simple assignment statements of this sort for QuickBooks objects:
transactionQuery.metaData = mdNoMetaData
(You can still use this sort of assignment for simple variables that you declare). Instead of =, the code uses the SetValue syntax. Similarly, here's a statement that obtains a value from QuickBooks, which appeared earlier in this chapter, in the section "Establishing the code":
ActiveSheet.Cells(RowNum, 3) = customerRet.Name.GetValue
Here the = is used as an assignment operator, but you need to use the GetValue action to actually access the value of the Name; then the assignment to the worksheet cell can occur. Just be aware of this and don't worry about it: If you omit a SetValue or a GetValue from a line of code that needs it, the compiler will complain and (unhelpfully) tell you that the property or method isn't supported. Most often, code that you get from the OSR will have any statements you need and will provide special syntax like SetValue or GetValue.
The SDK has a special variable called iterator. It is a way of breaking records returned from QuickBooks into chunks, so if you have 100 records to retrieve from QuickBooks, you could obtain them in 10 chunks of 10 records each. The iteratorID tells you which chunk you're working on. I have found little opportunity to use iterators in the context of quantitative analysis.
The ORCustomerListQuery works as a record filter. Although it is specific to customers and used only in the CustomerQuery message, it works largely in the same way as filters in other messages; for example, the InvoiceQuery message uses an ORInvoiceQuery filter, and the VendorQuery message uses an ORVendorListQuery. Some query messages use a more generic name for their filters; for example, both the ItemInventoryQuery message and the ClassQuery message use the name ORListQuery for their filters.
Regardless of the name, these filter elements subsume several sub-elements that act as filtering criteria. Your code specifies a criterion and assigns a value to it; when QuickBooks processes the request message, it returns only those records that meet the filter's criterion. In the case of the ORCustomerListQuery, for example, you can specify any one of the following sub-elements and criteria:
Using this criterion, you include one or more IDs in your request. Each member of the list (in this example, each of the customers in the company file) has a unique ID. If you have access to those ID values — perhaps because you have already run a query that has returned the IDs to you — then you can specify the IDs to restrict the records to those IDs.
Each record in a list has a unique ID, but it also has a unique full name. You can use the FullNameList to specify full names for your query to return. In the case of the CustomerQuery message, the full name follows the familiar Customer:Job pattern, such as "Abercrombie, Kristy:Family Room."
If you specify this criterion, there are several subelements that you can set. These subelements are not mutually exclusive.
Use the
MaxReturnedelement to limit the number of records returned to a value that you specify.The
ActiveStatuselement allows you to specify active records only (the default), inactive records only, or either.Use the
FromModifiedDateand theToModifiedDatecriteria to establish a range of dates during which records might have been modified; only records that have been modified within that range are returned.The
TotalBalanceFilterreturns records with an open balance that you specify. You call for QuickBooks to return records with an open balance that is less than, equal to or less than, equal to, greater than, or equal to or greater than the amount you specify. Obviously, this is useful for locating customers who owe you a certain amount of money, or more.You can also specify an
ORNameFilterfilter, explained in more detail later.
It's important to note that there are some mildly complex rules about which elements can be used to create filters. Don't worry about memorizing the rules, because they are embedded in the sample code you can get from the SDK. But you should know that these rules exist to understand what the code is doing with them.
More specifically, in the CustomerQuery message, you can set either an ID list, a full name list, or a customer list as the filter. But if you choose the CustomerListFilter, you can mix and match the MaxReturned element, the ActiveStatus element, the FromModifiedDate and the ToModifiedDate, and the Name list as you prefer: use MaxReturned and omit ActiveStatus, or use only the date criteria, or use ActiveStatus and NameList, and so on as you see fit.
The ORNameFilter has both a name-by-name filter, the NameFilter, and a range filter, the NameRangeFilter. Use the former to specify a name or names, and the latter to specify two names and all names between them.
Before you decide to use the NameFilter it's useful to understand how it may return different results from the FullNameList filter, explained earlier in this section. When a hierarchy exists in a list, using the FullNameList filter returns only the records for which you supply the criteria. In the CustomerQuery message, for example, if you specify that the FullNameList criterion is "Abercrombie, Kristy:Family Room," you will get only the record for that particular customer and job.
If you use "Alexander, Kristy" as the criterion for the FullNameList filter, QuickBooks returns "Alexander, Kristy" as both the customer name and the customer full name.
Things are different with the NameList filter, which pays attention not to the combination of customer name and job, but just to the customer name. In the Rock Castle Construction sample file, these four records exist for the customer named "Alexander, Kristy":
"Alexander, Kristy"
"Alexander, Kristy:Family Room"
"Alexander, Kristy:Kitchen"
"Alexander, Kristy:Remodel Bathroom"
In contrast to the FullNameList filter, if you use the filter ORNameFilter and call for the name to equal "Alexander, Kristy" then QuickBooks returns all four records, because the customer name associated with each of them is "Alexander, Kristy."
Using the NameRangeFilter enables you to specify a "from" name and a "to" name. QuickBooks returns those two names, and all names between them. If you omit the From name, QuickBooks starts at the beginning of the list; if you omit the To name, QuickBooks returns records through the end of the list.
Be sure the "from" name comes alphabetically earlier than the "to" name. Specifying "from" as "Smith, Bill" and "to" as "Babbitt, George" results in an error.
Normally, all available fields are returned from QuickBooks for each record that meets any filter criteria you have set. In the case of the CustomerQuery message, more than 90 fields would be returned.
For most business analysis applications, that's not a serious issue. You request the data set once and analyze it. That's usually the end of the road, at least for the current accounting period. On the other hand, if you're constantly retrieving data for customer service reps, it might put a strain on your network to return 90 fields to the service rep when only three or four fields are needed.
And if you have tens of thousands of records to retrieve from QuickBooks, those 90 extra, unneeded fields could easily make a difference in the time it takes to process your request. Even if you only send off the request once a month, why wait so long for the data to come back?
You can trim down the list of fields that QuickBooks sends back to you by using the IncludeRetElementList. Just name the fields you want returned; only they will traverse your network, assuming you're using one. You do need to be sure that you don't later try to do something with a field that you didn't call for; that would cause a runtime error in your code.
In QuickBooks, you can create custom fields in a company file. (This book isn't a QuickBooks user's guide, but if you're unfamiliar with the process, one place to start is to open the Customer Center, click the Edit Customer button in the Customer Information pane, and then click Define Fields.) You can retrieve custom fields in a message such as CustomerQuery. But you have to tell QuickBooks who you are.
Custom fields are public; that is, they're visible to anyone who's allowed to view data in the company file. So, although you have to supply what's called an owner ID to view custom fields, everyone has the same ID. It's 0 (that's zero).
You can also create fields that are called private data extensions. You create them, and access them, only via the SDK. You cannot use the QuickBooks user interface to view or edit a private data extension. Even through the SDK, you need to supply a lengthy ID value. If you are retrieving a private data extension in your message, you submit that ID here.
In sum, if you want to obtain data from a custom field, you (and everyone else) have an owner ID of 0. If you created a private data extension (and thereby obtained a lengthy, nonzero owner ID), use that ID in the OwnerIDList element.
You've already seen a highly abbreviated version of the CustomerQuery request code earlier in this chapter, in the section "Establishing the code." Subsequently, in the section "Elements in the CustomerQuery message," you saw coverage of all the elements that can be called out in a CustomerQuery request. This section puts the two together, in the form of code for the request as it comes directly from Version 6 of the SDK.
The code is commented in the SDK, but the comments are mostly tautological, and there are no examples of exactly how you supply, for example, a filter criterion. In this section, I hope to spare you the hours of trial and error I spent playing hide and seek with the SDK documentation. (I'm no SDK newbie, but it still took me hours to get the code to run to completion the first time out.)
Before you can run any code supplied by the SDK, there are a couple of quick steps you need to take. You need to establish a place to put the code and to tell the code where to find the DLL (see this chapter's earlier section, "Accessing QuickBooks objects.")
I'll assume that you want to run the code from the Excel platform, but that's not a requirement. You could just as easily run it from any application that can interpret Visual Basic. In Excel, you would take these steps.
Open a new Excel workbook. Press Alt+F11 to open the Visual Basic Editor.
Choose Insert
Switch to the Onscreen Reference. Reminder; after you have installed the SDK, you'll find the OSR in your Start menu, under QuickBooks SDK 6.0.
Select CustomerQuery from the Select Message dropdown (refer to Figure 11.3). The window shown in Figure 11.4 appears.
Click the VB6 Code link. The code window shown in Figure 11.5 appears.
Right-click in the code window and choose Select All from the contextual menu.
Press Ctrl+C to copy the selected code. Switch back to the Visual Basic Editor.
Press Ctrl+V to paste the selected code into the new module sheet.
As mentioned earlier in the section "Extensions to Basic," you need to make the QuickBooks object model available to your code using these steps:
With the Visual Basic Editor active, choose Tools
Scroll down the Available References list box until you find the reference to the qbFC6 1.0 Type Library (that's the DLL file). Fill its checkbox and click OK.
Switch back to the Excel workbook window and save your workbook. So doing saves the code that you have pasted from the SDK into the module, and also saves the reference you set to the qbFC6 library.
Note
You do not need to establish a reference to the qbFC6 library more than once in a given Excel workbook, no matter how many modules and subroutines it contains.
After I have pasted code from the OSR into a VBA module, I find it valuable to insert two option statements at the very top of the module, before any lines of code. The first is:
Option Explicit
This option requires that all variables be declared before they can be used. Most of the variables I use when I code analysis procedures for QuickBooks data are supplied by the SDK, and the SDK always declares the variables that it uses.
Figure 11.5. The statements that begin with an apostrophe are comments. You can replace or delete them if you want.
Figure 11.6. Be sure you fill the qbFC6 1.0 Type Library checkbox. It's easy to just click on the reference but forget to fill its checkbox.
But somewhere between occasionally and frequently, I want to use variables other than those in the SDK's code. For example, I might want to use a variable named ScratchSheet that contains the name of an Excel worksheet where I'm doing some intermediate calculations on data that I've brought back from QuickBooks. If I have put Option Explicit at the top of the module, I am forced to declare and use the ScratchSheet variable using something like this:
Dim ScratchSheet As String ScratchSheet = "Sheet1"
Then if I later try to use the ScratchSheet variable but erroneously type "ScrathSheet" as follows:
ScrathSheet = "Sheet2"
Visual Basic will complain that I'm trying to use a variable that has not been declared. My attention is drawn to the statement and I see that I inadvertently left out a "c" in the variable's name. I make the correction and move on.
But the default is to allow you to declare variables on the fly, simply by using them. If I had not used Option Explicit, Visual Basic would not complain when it encountered a name that I had not previously declared. I would not be alerted to the fact that I had mistyped a variable name. The result might be entirely benign, but on the other hand I might wind up with a problem when I didn't find what I expected to find on Sheet2. And it could take me a while to figure out what went wrong.
There's an option you can set in the Visual Basic Editor for any Microsoft Office application. With the Visual Basic Editor window active, choose Tools
I use memory arrays in much of my code for QuickBooks. I like to store data that comes back from QuickBooks in arrays rather than in worksheets or database tables when it's feasible to do so. (There are various reasons for that, beyond both the scope of this book and the limits of your patience.) And I like my arrays to start with element number 1. That is, I prefer the first element in an array to have the index 1, the second element to have the index 2, and so on.
Unfortunately for me, the default in VBA is for the first element in an array to be indexed as 0, the second element to be indexed as 1, and so on. Again, there are some fairly slick reasons for that arrangement to be the default, but I don't need any of those reasons. It saves wear and tear on my neurons for the first element in an array named CustArray to be CustArray(1), the second to be CustArray(2), and so on.
I can arrange for this by placing Option Base 1 at the top of the module, right below Option Explicit. There is no automatic option that you can set to arrange for this and it's just something that I remember to do.
You might prefer not to use Option Base 1 in code that you prepare for the analysis of QuickBooks data. QuickBooks lists that come back in result messages do not conform to the Option Base 1 specification; they start at element number 0 no matter what you do. You might find it annoying to use one indexing system for your own arrays but be forced to accept a different indexing system for QuickBooks lists.
Now it's time to make adjustments to the code you obtain from the SDK, so that it will do what you want it to. I'll step you through the four subroutines — two from a request message and two from a response message — that you can paste into modules in your Excel workbook. By following the comments that I put in the code, and simultaneously examining the code itself, you will be able to adapt the SDK's code so that it retrieves the data you're after and puts it where you want it. The request and the response are both from the CustomerQuery message.
Note
Although this lengthy example pertains specifically to a query about the customers in a QuickBooks company file, other sorts of queries follow the structure you see here very closely. You'll find after a little study that you can use the same techniques with other QuickBooks objects, such as items, accounts, reports, transactions, and so on.
Request messages that you find in the SDK all follow the same basic structure. They begin with a main subroutine (in the CustomerQuery message, it's named DoCustomerQueryRq) that establishes some variables and prepares to initiate a QuickBooks session. Then another subroutine (named BuildCustomerQueryRq) is called, which actually structures the query: Options are set and filters are specified. Control returns to the main subroutine, which passes the message to QuickBooks.
After QuickBooks has finished processing the request, it returns a response to your code. The main subroutine passes the response along to a third subroutine, which checks the validity of the response. That third subroutine is named ParseCustomerQueryRs in this example, and is one of two that form the response part of the CustomerQuery message. ParseCustomerQueryRs passes a valid response along to ParseCustomerRet, which handles the task of getting values from the response and putting them where you can use them: in variables or, if you arrange things properly, in worksheet cells.
Tip
Again, the names of these subroutines are easily generalized to other queries. For example, if you were establishing an inventory item query, the subroutines as named by the SDK would be DoItemInventoryQueryRq, BuildItemInventoryQueryRq, ParseItemInventoryQueryRs, and ParseItemInventoryRet. All that changes is the name of the query, from Customer to ItemInventory.
Note
Sometimes a line of code needs to wrap to the next available line, whether that's in a code module or on a page in this book. (The next statement is an example: it's too long to fit on one line.) In that case, you need to indicate that the statement continues on the next line, and in Visual Basic for Applications you do that by means of a space and an underscore (_). So, in the next statement shown here, there's a space and an underscore after the second As keyword. This indicates that the statement has not concluded but is continued on the next line.
Here's the code:
Public Sub DoCustomerQueryRq(country As String, majorVersion As _ Integer, minorVersion As Integer)
Right off the bat, there's an issue. The variables country, majorVersion, and minorVersion in the parentheses are called arguments. At the moment the subroutine begins, those arguments are supposed to contain values. So I like to add a short subroutine, usually at the very top of the module, that supplies values to the arguments and simultaneously calls the main routine. It usually looks something like this:
Sub RunQuery()
DoCustomerQueryRq("US", 6, 0)
End SubWith that subroutine in place, I can put my mouse pointer on its Sub statement, and press F5 to run it because it has no arguments in its Sub statement: The F5 key has no idea what an argument is. The next statement calls the SDK's DoCustomerQueryRq and supplies values for the arguments it uses.
Now back to the code from the SDK:
On Error GoTo Errs
As you'll see later, there's a segment of the code identified by the label Errs. The On Error statement says that in case of an error (for example, the code refers to a field name that does not exist in QuickBooks customer records), control flows to the Errs label and the code that immediately follows it. That code arranges for orderly termination of the subroutine in case of an error; if no error occurs, that code is not run.
The following four statements declare and assign values to two Boolean variables. The statements are explained earlier in the section "No runtime errors," but, briefly, they set two flags that prove useful in case the error recovery code needs to be run.
Note that the SDK code declares variables, by means of Dim statements, just before the point where they are first used. Many people, myself included, who cut their coding teeth on the Pascal language much prefer to declare variables at the beginning of the subroutine that uses them. If you're among the enlightened, feel free to move all the Dim statements to the top of the subroutine. If you do so, you might find in an almost vanishingly small number of cases that you need to reinitialize the variable at the point that the SDK had declared it.
Dim bSessionBegun As Boolean bSessionBegun = False Dim bConnectionOpen As Boolean bConnectionOpen = False
An object called sessionManager is declared and, using the New keyword, created. The aptly named session manager has various useful actions associated with it, such as the ability to open and close a connection to QuickBooks, to establish a request message that is passed to QuickBooks and a response message that is received from QuickBooks, and so on.
Dim sessionManager As New QBSessionManager
Then the message itself is declared. The session manager assigns the message certain attributes that are determined by the country, major version, and minor version. (The country refers to the QuickBooks edition; for the U.S., for Canada, and so on. The versions refer not to QuickBooks but to the version of the SDK that's in use.)
Dim requestMsgSet As IMsgSetRequest Set requestMsgSet = sessionManager.CreateMsgSetRequest(country, _ majorVersion, minorVersion)
The Visual Basic Editor in Microsoft Office applications has an option that lets you determine what happens in the event of an error; for example, break on all errors, break on errors that you haven't allowed for, and break on errors in a special kind of module called a class module. You can also specify in the request message what should happen in the event of an error. The following statement calls for QuickBooks to continue processing requests even if it encounters a request with an error in it. You can use roeStop instead of roeContinue if you want QuickBooks to stop processing requests when it finds one with an error.
requestMsgSet.Attributes.OnError = roeContinue
With the request message set established, it's time to tailor it to your requirements — in particular, any record filters that you want to include. The main subroutine calls the BuildCustomerQueryRq subroutine, with the request message set and the country version of QuickBooks as its arguments. The BuildCustomerQueryRq subroutine is explained later, after the conclusion of the main DoCustomerQueryRq subroutine.
BuildCustomerQueryRq requestMsgSet, country
After the characteristics of the request message set have been determined, the connection to QuickBooks can be opened and the session begun. Note that the two Boolean variables declared earlier are now set to True, as soon as the connection is opened and the session begun.
sessionManager.OpenConnection "IDN", "Sample QBFC Code" bConnectionOpen = True sessionManager.BeginSession "", omDontCare bSessionBegun = True
Now declare a response message set: The request message set has been prepared, and will tell QuickBooks what you want it to do. QuickBooks also needs a response message set into which it will place the results of performing the request.
Dim responseMsgSet As IMsgSetResponse
Pass the request message set to QuickBooks by attaching it as an argument to the session manager's DoRequests action. In effect, the next statement says this: "Session manager, take the request message set that we've built and have QuickBooks comply with the request. Put the results in the response message set that I just declared."
Set responseMsgSet = sessionManager.DoRequests(requestMsgSet)
Now that the request has been processed, end the session and close the connection. Set the corresponding Boolean variables to False.
sessionManager.EndSession bSessionBegun = False sessionManager.CloseConnection bConnectionOpen = False
The response from QuickBooks is in hand, but you can't see its results. Call the subroutine ParseCustomerQueryRs with the response message as one argument and the country as the other. The ParseCustomerQueryRs subroutine prepares the response data for placement, in this case to an Excel worksheet.
The reason that the country argument originated in the main subroutine and is passed to other subroutines is that some of the code in those subroutines might be executed, or skipped, depending on which country is represented in QuickBooks itself. As it happens, the BuildCustomerQueryRq subroutine does not adjust its request according to the value of country; the argument is there only because it's there in many other SDK subroutines that build message requests. But, as you'll see, the response makes use of the country argument; for example, if the country is something other than "US" then a value for the country's sales tax is returned in the response.
ParseCustomerQueryRs responseMsgSet, country
After the data has been parsed by the ParseCustomerQueryRs subroutine, the next statement terminates the main subroutine DoCustomerQueryRq. If some other procedure called the subroutine (for example, the simple three-line subroutine RunQuery shown near the beginning of this section) then control returns to it. Otherwise, the subroutine ends and execution is halted.
Exit Sub
However, suppose an error has occurred in the execution of the code (as distinct from during the processing of the request; an error there is handled by the message set's OnError attribute, explained earlier). Then the flow of control comes to the section of the subroutine headed by the label Errs — also explained earlier in this section.
Errs:
The code as supplied by the SDK first displays a message box with the identifying number of the error that occurred, along with its description. I tend to leave this statement in my code as long as I have it under development. When I believe that my product is ready to be used in a production setting, I remove it — it's unlikely to help any end user — and replace it with a message that urges the user to contact my company if the message appears.
MsgBox "HRESULT = " & Err.Number & " (" & Hex(Err.Number) & ") " _
& vbCrLf & vbCrLf & Err.Description, vbOKOnly, "Error"I always remove the next statement, which assumes the presence of a control — actually, a user form — named SampleCodeForm with a text edit box. I never have such a form in my VBA projects, and so I remove the statement, which merely duplicates some of the functionality of the prior MsgBox statement, to prevent the code from trying to find a form that isn't there.
SampleCodeForm.ErrorMsg.Text = Err.Description
Finally, in the event an error has occurred, some cleanup work is required. The code is about to stop running and in that case you don't want to keep a QuickBooks session going or a connection open. So, test whether the session has begun and has not ended; if so, end the session. Test whether the connection is open; if so, close the connection. Then end the subroutine.
If (bSessionBegun) Then sessionManager.EndSession End If If (bConnectionOpen) Then sessionManager.CloseConnection End If End Sub
The next section looks at the subroutine that actually builds the request for data.
The BuildCustomerQueryRq subroutine needs much more editing than the DoCustomerQueryRq subroutine. The reason is that DoCustomerQueryRq sets up some standard processes, such as establishing a session and a connection with QuickBooks. But building the query requires you to tailor it to your own needs: how the available filters should work if you want to use them, whether you want to restrict the fields that are returned, and whether you want access to any custom fields or private data.
There are times that I don't want to do any tailoring at all. Then, I might simply take the two absolutely necessary statements from the BuildCustomerQueryRq subroutine and put them in the DoCustomerQueryRq subroutine, removing the call to BuildCustomerQueryRq. Those two statements declare and set the customerQuery variable, and are the fifth and sixth statements shown later, in the BuildCustomerQueryRq subroutine.
Notice that BuildCustomerQueryRq takes two arguments, both passed to it by DoCustomerQueryRq.
Public Sub BuildCustomerQueryRq(requestMsgSet As IMsgSetRequest, _ country As String)
I have never seen the following If test evaluate to True; given that the code gets this far, the requestMsgSet must have been declared correctly. However, there's almost no cost to leaving the test in place. If for some reason the If test evaluates to True, then control returns to DoCustomerQueryRq and the error handler in that subroutine will take over.
If (requestMsgSet Is Nothing) Then Exit Sub End If
Here are the two statements you absolutely must provide. You need to tell QuickBooks which query to execute, and you do that by appending the customer query request to the request message set:
Dim customerQuery As ICustomerQuery Set customerQuery = requestMsgSet.AppendCustomerQueryRq
If you don't want metadata (which in QuickBooks queries tells you approximately how many records your query can return) you can omit the next statement, because the default is, as shown, no metadata. If you want metadata only, instead of mdNoMetaData use mdMetaDataOnly. If you want both metadata and the actual records, use mdMetaDataAndResponseData.
customerQuery.metaData.SetValue mdNoMetaData
When you set the reference to the qbFC6 object library (see the section "Laying the groundwork" earlier in this chapter) you get a side benefit. Suppose that you have edited the metadata statement so that it looks like this:
customerQuery.metaData.SetValue
When you type a space after SetValue, the Visual Basic Editor responds by displaying a contextual menu that contains all the possible values the metaData element can take on. You don't need to remember them or look them up in a document. But you do need to know what each of the possible values means, so that you can select the one you want.
The way to get the number of records from metadata is described shortly, in the section "Understanding the ParseCustomerQueryRs subroutine." It takes only one statement.
I seldom have needed to retrieve data in chunks, so I usually omit the next two statements. The iterator is explained earlier in this chapter, in the section "iterator and iteratorID."
The code as supplied by the SDK often supplies "val" as a sort of placeholder, where a value is needed and there are too many possible values to display in a contextual menu. In those cases, you must either supply the value you want or omit the statement. If you just leave "val" in place, it will normally result in a runtime error. (The exception occurs when "val" is a syntactically permissible value. But syntactically permissible is not the same thing as meaningful.)
customerQuery.iterator.SetValue itStart customerQuery.iteratorID.SetValue "val"
At this point in the BuildCustomerQueryRq code, the process of structuring the filters begins. If you do not want to use filters at this point (and if you want all fields except custom fields), you can omit everything from here to the End Sub statement.
I often omit the remaining code. I prefer to bring all the data for a particular type of query back from QuickBooks and do any necessary filtering later. An exception occurs when I am requesting transaction data; then, it usually turns out that I want to filter for transactions that take place between a given start date and end date. Ordinarily there are many more transactions than there are customers, so I want to trim down the volume. But when I'm dealing with a list of at most a few hundred customers, I prefer to keep the code to a minimum and omit the filters. That makes the code a lot easier to maintain.
Assuming that you want to use the filters for the CustomerQuery message — or at least understand how they work — they are repeated here as the SDK provides them.
First a simple string variable is declared. It contains the name of the filter you want to use. At this point the filters are mutually exclusive, so setting the string variable to a particular value is tantamount to selecting which filter you want to use.
Dim orCustomerListQueryORElement1 As String orCustomerListQueryORElement1 = "ListID"
The code that the SDK provides assumes that you want to use ListID as a filter. As the code is supplied, you could instead choose FullName or CustomerListFilter. You would do that by using one of these statements instead of the one supplied by the SDK:
orCustomerListQueryORElement1 = "FullName"
or
orCustomerListQueryORElement1 = "CustomerListFilter"
At this point the SDK code sets the filtering criteria according to your choice of filter:
If (orCustomerListQueryORElement1 = "ListID") Then
customerQuery.ORCustomerListQuery.ListIDList.Add "val"Assuming you want to use the ListID filter, there are some points that you should keep in mind:
The value "val" is not and never will be a QuickBooks customer ID. If you want to filter using ListID, replace "val" with a legitimate ID value.
A customer ID in QuickBooks is a lengthy string, something such as 80000001– 1250100403. It would be crazy to try to actually type a value like that into your code. It becomes more rational if you already have those ID values stored in, say, an Excel worksheet or a database table. Then you could arrange to loop through the ID values and add them to the request message.
The ListIDs used in the CustomerQuery identify full names; they correspond to fully qualified records of the Customer:Job sort.
If for some reason you find it necessary to filter for a few customers using their ListIDs, you can use a structure like this:
If (orCustomerListQueryORElement1 = "ListID") Then customerQuery.ORCustomerListQuery.ListIDList.Add _ "70000001-1250100403" customerQuery.ORCustomerListQuery.ListIDList.Add _ "80000001-1250100403" customerQuery.ORCustomerListQuery.ListIDList.Add _ "90000001-1250100403"
The SDK code continues by checking to see if you want to use a full name instead of a ListID.
ElseIf (orCustomerListQueryORElement1 = "FullName") Then
customerQuery.ORCustomerListQuery.FullNameList.Add "val"Again, don't leave "val" where it is. Replace it with "Abercrombie, Kristy:Remodel Kitchen" or some other full name from your company file. A full name in the case of a CustomerQuery message consists of the customer name and a job name, separated by a colon. It's not quite as crazy to use a FullNameList in code as it is to use ListIDs in code, but it's not a good idea. Unless there's a compelling reason to do so, it's seldom wise to hard-code values such as these into your code. There are usually better ways, such as loops that pick up values from elsewhere, or arguments passed from calling procedures.
As is the case with the ListID, if you have more than one full name to use you can stack several Add statements.
Last, you might choose to use the CustomerListFilter rather than the ListID or the FullName.
ElseIf (orCustomerListQueryORElement1 = "CustomerListFilter") Then
If you use CustomerListFilter, you have access to several filters that can be used in combination. I don't use the MaxReturned setting unless I'm in the early stages of debugging my code, when a careless mistake is likely to cause a problem whether I'm getting 10 records or 1,000.
customerQuery.ORCustomerListQuery.CustomerListFilter.MaxReturned. _ SetValue 10
Change the value 10 to some other value if you want. You can omit the statement entirely if you don't want to put an arbitrary cap on the number of records to return from QuickBooks.
To get only customers who are currently defined as active, use this statement or omit it entirely:
customerQuery.ORCustomerListQuery.CustomerListFilter.ActiveStatus. _ SetValue asActiveOnly
The default is asActiveOnly, as given by the SDK. You could also use asInactiveOnly or asAll.
To return only customer records that have been modified during a particular range of dates, specify both a "from" date and a "to" date, as follows:
customerQuery.ORCustomerListQuery.CustomerListFilter _ .FromModifiedDate.SetValue #2003/12/31 09:35#, False customerQuery.ORCustomerListQuery.CustomerListFilter _ .ToModifiedDate.SetValue #2003/12/31 09:35#, False
Notice that the two dates given earlier are identical. This is merely a quirk of the code as supplied by the SDK. Normally you'll want to set the "from" date earlier than the "to" date. You can omit the time of day if you want. Note the use of # to delimit the date/time values — that's how you distinguish the date/time data type from other types.
You could also pass a "from" and a "to" date to the subroutine from a calling subroutine. In that case, you would assign the dates of interest to variables and pass the variables as arguments. Don't use #s if you use this method; instead, declare the variables that you pass as the Date type.
You can use MaxReturned, ActiveStatus, and modification dates with one another, and you can put the next filter, orNameFilterORElement2, into the mix as well. However, there are two filters that can be specified by orNameFilterORElement2, and you can use only one. You can specify a particular customer name or you can specify a range of names, but not both. So the SDK code uses another If structure. First, the variable that holds your choice is declared, and then it's assigned a value; the SDK's choice is to give it the value NameFilter:
Dim orNameFilterORElement2 As String orNameFilterORElement2 = "NameFilter"
If you're using the NameFilter, you must begin by deciding what sort of match you want for your criterion. Suppose you supply the criterion value "Arp." Then, if the criterion type is StartsWith, QuickBooks will return customer names such as Arpel and Arpent. If the cri- terion type is Contains, QuickBooks returns customer names such as Carpenter. And if the criterion type is EndsWith, you get customer records with names such as Garp.
Although any or all of the filters explained here can be completely omitted from your code, you must supply specific information if you decide to use the NameFilter. You can't just supply a name or part of a name. Your request must include the type of match you want: mcStartsWith, mcEndsWith, or mcContains.
If (orNameFilterORElement2 = "NameFilter") Then
customerQuery.ORCustomerListQuery.CustomerListFilter _
.ORNameFilter.NameFilter.MatchCriterion.SetValue mcStartsWith
customerQuery.ORCustomerListQuery.CustomerListFilter _
.ORNameFilter.NameFilter.Name.SetValue "val"Don't forget to change the "val" supplied by the SDK to an actual customer name or name fragment. In this case, because the customer name is a string variable of indeterminate length, you could get Valjean, Corvallis, or Stoval, depending on the criterion type you choose.
You can specify an alphabetical range of names instead of a specific name. The range's "from" name must precede the "to" name alphabetically. If you're going to filter in this fashion, adapt the following statements by replacing the two instances of "val" with the two name values that frame the range of names you're after:
ElseIf (orNameFilterORElement2 = "NameRangeFilter") Then
customerQuery.ORCustomerListQuery.CustomerListFilter _
.ORNameFilter.NameRangeFilter.FromName.SetValue "val"
customerQuery.ORCustomerListQuery.CustomerListFilter _
.ORNameFilter.NameRangeFilter.ToName.SetValue "val"
End IfJust to keep perspective, it helps to back up and review where we are. At this point, the code is building a request message that will be submitted to QuickBooks. The request pertains to customer information in QuickBooks' active company file. The request can have record filters that determine which customer records are returned from QuickBooks, as follows; only one of the three options can be used:
You can choose to filter on customers'
ListIDs, which are lengthy values assigned by QuickBooks, and which you'll have to retrieve from QuickBooks before you can use them as filter criteria.You can choose to filter on customers' full names, using the Customer:Job pattern.
Or you can choose to filter according to what the SDK terms the
CustomerListFilter. In that case, you can specify one or more ways to limit the records that are returned:MaxReturned, ActiveStatus, a range of modification dates, and either a customer name filter or a range of customer names.
There is one more filter you can apply, the customer's total open balance. As is the case with the NameFilter, you have to select the type of criterion you want to use. The code supplied by the SDK assumes that you'll want to retrieve customers whose open balance is less than your criterion value, and so it supplies the criterion type oLessThan:
customerQuery.ORCustomerListQuery.CustomerListFilter _ .TotalBalanceFilter.Operator.SetValue oLessThan
More often, you're going to be interested in customers whose open balance is equal to or greater than some figure, so you'd use oGreaterThanEqual instead of oLessThan.
Then you need to specify the value of the criterion itself:
customerQuery.ORCustomerListQuery.CustomerListFilter _ .TotalBalanceFilter.Amount.SetValue 2.00
Obviously, you should change the sample value of 2.00 to whatever criterion you want to use for the customer's open balance.
The final End If closes the lengthy If ... Then ... Else structure that characterizes the BuildCustomerQueryRq subroutine. (It was initiated at the outset by testing whether you set orCustomerListQueryORElement1 to "ListID".)
End If
The section of the code that pertains to filtering records has now concluded. You can at this point provide or omit one or more statements that specify the particular fields you want QuickBooks to return. Simply name the fields in statements like this one:
customerQuery.IncludeRetElementList.Add "val"
Replace "val" by the name of a field you want to return. Add one or more similar statements to specify additional fields.
You do need to know the names of the fields, and there are two reasonably convenient places to find the relevant field names. One is in the subroutine named ParseCustomerRet, which is covered in the next section. It names all the available fields in expressions like customerRet.FirstName.GetValue. In that expression, customerRet identifies the source and type of data, FirstName is the name of the field, and GetValue actually returns the FirstName value in the current customer record.
Another and perhaps more convenient place to see a list of the available field names is in the OSR itself. Refer to Figure 11.4. In the window displayed there, if you click Response, the elements that pertain to the response message appear, as shown in Figure 11.7. Those elements are the available field names.
The list shown in Figure 11.7 is much more compact than the code in the ParseCustomerRet subroutine, but you have to switch to the OSR to access it.
Finally, the last specification in the BuildCustomerQueryRq subroutine is:
Figure 11.7. The first few fields of almost 90 that you can retrieve from QuickBooks customer records.
customerQuery.OwnerIDList.Add _
"{22E8C9DC-320B-450d-962A-87CF7246D080}"The statement specifies an owner ID, a random one in the SDK's sample code, which is the ID assigned to the user who created a private data field. As explained earlier in this chapter, in the section "The OwnerIDList element," you can use the SDK to create private data, a sort of custom field that can be accessed only through the SDK. But you can access the private data only if you supply the owner ID that belongs to the creator of the private data field.
If there is private data in the company file, you need to supply the owner ID (or the owner IDs, if there are more private data fields), and the previous code statement is where you do that. If no private data exists in the company file, or if you're not interested in it, do not supply the ID.
However, the company file might contain custom fields, which you can create using the QuickBooks user interface. Those fields are not considered private, but you still need to supply an owner ID to access them via the SDK. Anyone can use the generic owner ID of 0, to get at custom data, as follows:
customerQuery.OwnerIDList.Add "0"
Note that the specific owner ID for private data is enclosed within curly brackets and quotation marks, but the generic owner ID is enclosed by quotation marks only.
The subroutine ends here:
End Sub
At this point, control returns to the subroutine that called BuildCustomerQueryRq. Using the structure offered by the SDK, that calling subroutine is DoCustomerQueryRq. If you refer back to that subroutine, in the section "Fine-tuning the code," you'll see that it next submits the request message set, as built in this section, to QuickBooks. After the DoRequests action is complete, a response message set is received from QuickBooks. It remains to do something with the response, and that process is the topic of the next section.
The CustomerQuery request code, as supplied by the SDK, consists of two subroutines. DoCustomerQueryRq performs housekeeping tasks such as declaring variables, establishing communications with QuickBooks, calling the subroutine that actually builds the query, accepting the response from QuickBooks, and forwarding the response. The BuildCustomerQueryRq subroutine actually assembles the elements of the query that define which records QuickBooks is to send back and which fields are to be included in the response.
Similarly, the response code comprises two subroutines: ParseCustomerQueryRq and ParseCustomerRet. The former subroutine performs some initial tests to verify that it's receiving the right kind of response and that the response is valid. If those tests are passed, the subroutine loops through the individual members of the response.
For each member in the response, the subroutine calls ParseCustomerRet, which actually gets the value on each field for each customer record. At that point it's up to you what to do with the data. The following section explains the ParseCustomerQueryRq subroutine, and the subsequent section explains the ParseCustomerRet subroutine.
In keeping with the approach I've taken in this chapter, I won't modify the code as supplied by the SDK. But I'll include information here and there to suggest how you might manage the data and the code. The assumption is that you are running the code from the Excel VBA platform, and you'll see how to save the QuickBooks data to an Excel worksheet. But the same concepts apply if you prefer to store the records and fields in a true database table or even some other location.
Much of the code in the ParseCustomerQueryRs subroutine will probably seem arbitrary or unnecessary. But keep in mind that it doesn't hurt and it might conceivably help; and also that it's been written for you, so that you don't have to modify or maintain it. Just copy it from the OSR into your code module.
Bear in mind that you can move all the Dim statements, which declare the names and types of variables, up to the start of the subroutine, immediately following the Public Sub state- ment. Also bear in mind that you can omit from the ParseCustomerRet subroutine any field you're not interested in. If you omit a field, your code will run a tiny bit faster and will be a little easier to maintain, but you'll have more work to do if you change your mind and decide you want it after all.
The processing of the response from QuickBooks begins with a statement that initiates the ParseCustomerQueryRs subroutine:
Public Sub ParseCustomerQueryRs(responseMsgSet As IMsgSetResponse, _ country As String)
As noted earlier in the section "Fine-tuning the code," the version of QuickBooks as determined by the country ("US", "CA", and so on) is passed to this subroutine because certain fields are available in some versions but not in others.
If (responseMsgSet Is Nothing) Then Exit Sub End If
As is the case with requestMsgSet in DoCustomerQueryRq, it doesn't hurt to include the earlier test, although you would have had to run ParseCustomerQueryRs without providing a response from QuickBooks to get any noticeable result from the test.
Declare and set a variable to hold the responses found in the response message set that was passed to this subroutine. Test that an actual list of responses (which might well contain one response only) has been assigned to the response list from the response message set. If that test fails, exit the subroutine; control will be returned to DoCustomerQueryRq's error-handling routine.
Dim responseList As IResponseList Set responseList = responseMsgSet.responseList If (responseList Is Nothing) Then Exit Sub End If
Now loop through the list of responses (again, there may be only one response). Use the variable i as the loop counter. Notice that the response list begins with response number 0. If there's only one response, then responseList.Count equals 1, and the loop will start at response number 0 and also end there. The GetAt action is used to access a full response, not merely a single value (then, the GetValue action would be used).
Dim i As Integer For i = 0 To responseList.Count - 1 Dim response As IResponse Set response = responseList.GetAt(i)
I use the next statement, or a version it, to help me figure out what I've done wrong. The response message has a status code of 0 if all has gone well so far. In that case, the code continues and returns the data that's been requested.
However, if the status code is a value other than 0, there's something wrong with the way I've structured my request. I can use that status code to help determine what the problem is.
As the SDK supplies the test for the response's status code, it looks like this:
If (response.StatusCode = 0) Thens
I prefer to structure it like this:
If (response.StatusCode <> 0) Then MsgBox response.StatusMessage Else
And then continue with the code as given by the SDK. The benefit I derive is that if I've somehow arranged for an error in my request, I'll see a message on my screen that gives me the information about the error. The message might tell me that a particular value is not allowed as a criterion for a filter.
If, instead of displaying response.StatusMessage, you display response.StatusCode, you must return to the OSR and click Errors at the top of the window. Doing so displays the window shown in Figure 11.8.
For example, if you see that message box and it tells you that the error code was 1, you know that you've done something wrong in setting up your filters: You've made their combined effect so restrictive that QuickBooks couldn't find a single customer that met all their criteria. However, displaying response.StatusMessage in preference to response.StatusCode often gives you better, more specific information than you can get from the OSR's error listing.
It is at this point in the code that you can obtain the metadata that tells you how many records have been retrieved. That statistic is stored in response.retCount, so you could store the result in a worksheet, perhaps in the 100th column of the first row, with something like this:
ActiveSheet.Cells(1,100) = response.retCount
Or give yourself a real time message with this:
MsgBox response.retCount & " records have been retrieved."
To continue with the code as given by the SDK:
If (Not response.Detail Is Nothing) Then Dim responseType As Integer responseType = response.Type.GetValue Dim j As Integer If (responseType = rtCustomerQueryRs) Then
Figure 11.8. The error numbers are accompanied by a clue to what you might have done wrong building the request.
The response detail contains customer-by-customer information; there's a record in the detail for each customer that has been returned from QuickBooks. The variable customerRetList is declared and the detail records are assigned to it.
Then the code loops through each customer record and hands it off to the final subroutine involved in this process, ParseCustomerRet. In that call, notice that the current member of the customer list is passed by means of the GetAt action. The value for country is also passed along so that the code can decide whether to look for specific fields in the response detail.
Dim customerRetList As ICustomerRetList
Set customerRetList = response.Detail
For j = 0 To customerRetList.Count - 1
ParseCustomerRet customerRetList.GetAt(j), country
Next j
End If
End If
End If
Next i
End SubAt the completion of the ParseCustomerQueryRs subroutine, control returns to DoCustomerQueryRq to make sure of an orderly completion of the process.
Here's the subroutine that actually gets hold of the customer's values for you to store as you think best:
Private Sub ParseCustomerRet(customerRet As ICustomerRet, country _ As String)
Test to make sure that there's actually something in the current customer record. If not, exit this subroutine and go back into the loop to get the next customer record:
If (customerRet Is Nothing) Then Exit Sub End If
The following two statements are typical of how the SDK's code accesses a field and makes it available to you. The first statement declares a variable of the proper type for each field. The second statement assigns the value that was returned from QuickBooks to that newly declared variable. Here's the code that corresponds to the customer's ID:
Dim listID1 As String listID1 = customerRet.ListID.GetValue
If you do nothing else with this code, the data eventually just goes to byte heaven. The SDK offers no code that you can use to save or otherwise process the value of the customer's ListID. When the subroutine has finished acquiring data from the response, control returns to the loop that called the subroutine. The subroutine is called for the next customer, the variable listID1 is declared again, and whatever value it held before has been lost.
Therefore, it's in this subroutine that you must make arrangements to save or process the data that comes back from QuickBooks.
Notice that the listID1 variable has the numeral 1 appended to its name. The next variable is declared as timeCreated2, the next is timeModified3, and so on. This approach helps avoid declaring the same variable twice, because each variable has a unique number appended to its name: declaring a variable twice in the same procedure causes a compile error. (In a few cases, a field's listID and the associated fullName share the same number; for example, the SDK's code for the CustomerQuery includes listID8 and fullName8.)
If you glance back at this chapter's section "Bringing the data into Excel," you'll see that I suggest assigning each value to a row that belongs to the current customer, in a column that belongs to a particular field. I pass a variable that I declare, named RowNum, from the loop in ParseCustomerQueryRs to the current subroutine, ParseCustomerRet. I increment RowNum by 1 each time the loop executes, and I write the current value to that row and to the column I have assigned to the current field.
Therefore in my adaptation of the SDK code, the loop in ParseCustomerQueryRs looks like this:
RowNum = 2 For j = 0 To customerRetList.Count - 1 ParseCustomerRet customerRetList.GetAt(j), country, RowNum RowNum = RowNum + 1 Next j
My version of the statement that initiates the ParseCustomerRet subroutine is:
Private Sub ParseCustomerRet(customerRet As ICustomerRet, country _ As String, RowNum As Integer)
Notice that RowNum has been added as an argument to the ParseCustomerRet subroutine. In this way, I can get the current row number from ParseCustomerQueryRs and into ParseCustomerRet for use in keeping track of where the data should be written.
With those arrangements made, here's what I do with the ListID field. You can use the same approach for any field returned from QuickBooks:
ActiveSheet.Cells (RowNum, 1) = customerRet.ListID.GetValue
Notice that with this approach you don't need to declare a variable such as ListID1, as the SDK's code does, and assign the current value to it. You simply write the value directly to a worksheet cell, where it's available later. But if you have some other or additional use for the value, you'll probably want to assign it to a variable as suggested by the SDK.
For example, when I'm bringing inventory data back from QuickBooks, I sometimes want to keep a running total of the quantity of a particular item that was bought or sold in a given transaction. I also want to write the current transaction's inventory quantity to a worksheet. To keep the number of accesses to the customer record to a minimum, I might use code such as this:
TxnQuantity = colData.Value.GetValue RunningTotal = RunningTotal + TxnQuantity ActiveSheet.Cells (RowNum, 5) = TxnQuantity
Note
The ListID value, for an object such as a customer or an item, and the TxnID, for I any transactions such as a sales receipt or a purchase, are necessary values if you want to delete either a member of a list or a transaction. I seldom delete a member of a list, by means either of the QuickBooks user interface or the SDK, but I sometimes find it necessary to delete inventory adjustment transactions. Use the TxnDel message to delete transactions and the ListDel message to delete members of lists. (You still can't delete a customer who's associated with a transaction or job, however.) If you want to use TxnDel or ListDel, you'll need to supply the object's ID, and to get that you need to run the appropriate query, such as the CustomerQuery I'm explaining in this chapter.
Continuing with the code as supplied by the SDK:
Dim timeCreated2 As Date timeCreated2 = customerRet.TimeCreated.GetValue
Dim timeModified3 As Date timeModified3 = customerRet.TimeModified.GetValue Dim editSequence4 As String editSequence4 = customerRet.EditSequence.GetValue Dim name5 As String name5 = customerRet.Name.GetValue Dim fullName6 As String fullName6 = customerRet.FullName.GetValue
You see the pattern. Declare a variable with the Dim statement, and then assign the appropriate field from the response to that variable. Again, you can add a statement that writes the value to an Excel worksheet or a database table if you want — and there's not much point to running the code in the first place if you're not going to do anything with the data.
At this point the SDK code introduces a wrinkle:
If (Not customerRet.IsActive Is Nothing) Then
Dim isActive7 As Boolean
isActive7 = customerRet.IsActive.GetValue
End IfRefer back to Figure 11.7, and notice that the final column on the right, labeled Guaranteed, contains some Y entries. The Y, standing for Yes, means that the associated element is guaranteed to be returned in the response from QuickBooks.
Given that a response passes the tests administered in the ParseCustomerQueryRs subroutine, the response is guaranteed to include a value for ListID, TimeCreated, TimeModified, and so on, through FullName. A little farther down the list, Sublevel is also guaranteed. (Sublevel indicates how far down the hierarchy the record exists: The customer is at the top, and the customer:job is one level down from the customer.)
However, the presence of values in the remaining fields is not guaranteed by the SDK. The first such field is the IsActive field. Suppose there is nothing in customerRet.IsActive for a particular customer. In that case, the statement will fail, and cause the code to stop with a runtime error. Therefore, the SDK code surrounds the variable declaration and assignment statement with an If test; if there's nothing in IsActive for the current customer, no attempt is made to declare and make an assignment to the IsActive7 variable — and an error is avoided.
isActive7 = customerRet.IsActive.GetValue
From this point to almost the end of the subroutine, the fields are not guaranteed to contain values, and so all the declarations and assignments use this structure:
If (Not customerRet.fieldname Is Nothing) Then
[Assignment Statement]
End IfHere's an alternative. An earlier section in this chapter, "No runtime errors," briefly covered the On Error GoTo Errs statement in the SDK code. A similar statement has a similar effect. If a runtime error occurs, the execution of the code is not interrupted, but instead continues at the next statement. In effect the error is ignored.
On Error Resume Next
In this long subroutine, that's exactly what we want to happen. If a value is missing for IsActive, or ParentRef, or CompanyName, or any of about 80 other fields that are not guaranteed, then an error will occur when the code tries to assign a missing value to a variable such as IsActive7, or to a worksheet cell such as ActiveSheet.Cells (RowNum, 7). No harm is done when On Error Resume Next is in effect; the code merely progresses to the next field.
In consequence, once I've set On Error Resume Next, I can trim down the code for any given field to one statement. Instead of this:
If (Not customerRet.ParentRef Is Nothing) Then Dim fullName8 As String fullName8 = customerRet.ParentRef.FullName.GetValue Dim listID8 As String listID8 = customerRet.ParentRef.ListID.GetValue End If
I can trim it to this:
ActiveSheet.Cells (RowNum,8)=customerRet.ParentRef.FullName.GetValue ActiveSheet.Cells (RowNum,9)=customerRet.ParentRef.ListID.GetValue
There is little point in repeating the remainder of the SDK code for the ParseCustomerRet subroutine, except for custom fields and private data, which are explained at the end of this section. As the code is provided by the SDK, all the remaining fields are dealt with by testing whether they contain legitimate values; if they do, a variable is declared and the corresponding value is assigned to that variable.
As an alternative, if you're interested in those fields, assign their values to the current RowNum in a worksheet, to a column that remains constant for that field. For example, the two statements immediately prior write the customer's parent's full name to column 8 — that's column H — in the worksheet, and the customer's parent's ListID to column 9. (There's nothing special about those columns; you can put the data from the fields in any column you find convenient or useful.)
If you're not interested in a field, you can delete the code that deals with it. However, I suggest that you consider merely commenting out the statement; you do this by inserting an apostrophe at the start of the statement. That changes it to a comment, which is non-executable. Such a statement will be skipped during the execution.
I suggest that you consider commenting out the code pertaining to those fields instead of deleting them because you might change your mind later. It's a lot easier to remove an apostrophe, and thus re-establish an executable statement, than it is to find it again in the SDK code and copy and paste it back into your module. Once you have your code working the way you want, and have run it several times without any problems, then it's more likely that you can safely delete the code.
Here is the code for the final standard field returned by the CustomerQuery. I include it so that you can orient yourself with the code as shown in the OSR:
If (Not customerRet.PriceLevelRef Is Nothing) Then
Dim fullName82 As String
fullName82 = customerRet.PriceLevelRef.FullName.GetValue
Dim listID82 As String
listID82 = customerRet.PriceLevelRef.ListID.GetValue
End IfThat If block ends the retrieval of the standard fields in the CustomerQuery. The remaining code deals with the retrieval of any custom fields or private data that might be in the company file. If there are no custom fields or private data, or if you're not interested in them, you can simply omit the following If block. Otherwise, include the code just as supplied by the SDK, but make the usual arrangements for saving the results:
If (Not customerRet.DataExtRetList Is Nothing) Then
Dim j As Integer
For j = 0 To customerRet.DataExtRetList.Count – 1More than one custom field can be defined, so the SDK code for CustomerQuery loops through as many such fields as exist in the company file's customer records.
Dim dataExtRet83 As IDataExtRet Set dataExtRet83 = customerRet.DataExtRetList.GetAt(j) If (Not dataExtRet83.owner ID Is Nothing) Then Dim ownerID84 As String ownerID84 = dataExtRet83.owner ID.GetValue End If
You had to supply owner ID in BuildCustomerQueryRq to get the custom fields or private data, so there's no special reason to capture it here.
Dim dataExtName85 As String dataExtName85 = dataExtRet83.DataExtName.GetValue
DataExtName is the name of the custom field or private data. For example, in the Rock Castle Construction file, there's a custom field named B-Day associated with the Customer record.
Dim dataExtType86 As ENDataExtType dataExtType86 = dataExtRet83.DataExtType.GetValue
You're unlikely ever to need access to the data type, so even if you're returning a custom field you can omit the DataExtType code.
Dim dataExtValue87 As String dataExtValue87 = dataExtRet83.DataExtValue.GetValue
The value stored in a custom field or private data is returned by DataExtValue.GetValue.
Next j
End If
End SubThe loop terminates, the If test for custom data ends, and so does the subroutine. Control passes back to ParseCustomerQueryRq and from there back to the original subroutine, DoCustomerQueryRq.
If you have worked your way through this presentation, you're well placed to generalize it to other SDK queries: for example, queries for lists such as accounts and items, and transactions such as bills and invoices. The basic structure is the same: two request subroutines (one to direct the communications and one to build the query specifics), and two response subroutines (one to test the response's validity and one to put the field values into variables). The names of the subroutines change, as do the names of the fields, but the concepts and structures are largely the same from query to query.
Reports via the SDK are a different matter, and are among the topics to be taken up in Chapter 12.