Daily Dose of Excel

Over the past few weeks, I’ve had reason to explore the use of VBA to access information on web pages and through web services using both InternetExplorer and XMLHttp. While my study of the two is far from exhaustive, I decided to document the research for general consumption expecting it to take a few hours. As I wrote more issues cropped up and the “few hours” project took several days. But, it’s finally uploaded at
VBA & web services
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

I imagine this is the way it is for most people:

Vast number of ideas of what I’d like to do
    A few get implemented as add-ins, utilities, tutorials, what have you
        Even fewer find their way to the website
            And, it takes even longer to find a proper location for very new topics.

I spent the better part of today adding one new tutorial, one new template, and hooking up several old pages that were already part of the www.tushar-mehta.com website but were never part of the site navigation structure. In the process, I added a new high level category called “Templates.” The following introduces each item.

Enhanced Find: This is a source form utility that I wrote for my own use. I imagine it would have been much simpler if Microsoft had added the UI ‘Find All’ feature to the Excel object model. But, it didn’t, and, in any case, the utility does a lot more than just ‘find all.’ To those who will want to tell me I should have used the TreeView control in the userform used to display the result: I did. {grin} Then, I decided it would be prudent to forgo it since I don’t know how that control finds its way onto a particular machine.

Chart Image to Data: I wrote this utility to help out someone who wanted to convert an image of a graph into the associated data points. No, it doesn’t use artificial intelligence, or OCR, or some fancy heuristic to figure out the data. {grin} Instead, it relies on the user first calibrating the image and then clicking on various points on a series to convert the click location into a data value.

The templates hierarchy: Several templates that I created for various reasons have languished on the website with no good place to put them. So, I finally broke down and added a high level category, Templates. As usual, whenever a file moves from one directory to another, I leave — or at least try to leave — a stub in the old location pointing to the new one.

Solver template to find a subset of entries that add up to a given total: One would expect that when someone makes a payment against a list of different charges (invoices), they would list which charges invoices are being paid. Yet, every so often we find a request for help from someone who has a total and now wants to find a possible subset of pending payments that total the payment amount. This template contains the necessary Solver set up for the task. Obviously, this is not an easy task and the ability to find an acceptable solution is limited both by the requirements and limitations placed on Solver and of the algorithm used by Solver.

A calendar template: Even if I say so myself, I think this is worth checking out. It’s a template that lets one generate a calendar for any year from 1900 to 3000 with the starting-day-of-week set to any weekday. The result can be in one of 2 formats — one month-per-page or 12 months-per-page. The design is clean and simple. And, best of all, no programming!

Compare sizes of TVs, computer monitors, and broadcasts: Over the past month or so, I replaced my dying TV with a HD TV and added a widescreen computer monitor. In the process, I spent a fair amount of time understanding what’s what with widescreen devices and HD broadcasts. This template addresses one specific aspect of that research: comparing the sizes of various devices. One thing I realized very quickly in my research was that a widescreen device would have to be somewhat larger than its standard counterpart if it was to have at least the same height. I did most of my work with algebra. But, then decided I’d share some of it in the form of a template. While creating the template I also realized it could be used to explain the bands that appear when one views a broadcast in one format (say widescreen) on an incompatible TV (i.e., a standard size TV). I also realized that the work in the template would form a good tutorial for data validation.

Data Validation I: There’s a whole bunch of stuff I have on data validation that I’d like to share. Given the big weakness in Excel’s native data validation (copy+paste into a cell to wipe out the validation criterion in it!), I prefer alternatives. This tutorial documents some simple ways to validate data without Excel’s Data Validation or VBA. There’s a whole bunch more I will share in the weeks/months to come.

Userform coding interface: I started work on a VBA chapter — and it is far from complete — that addresses two favorite topics of mine: (1) The structure of the interface between the code that manages a userform and the code that does the actual task of a utility, and (2) The RefEdit control including a workaround that lets one simulate its functionality in a modeless userform, together with a class module that lets one “drop” the solution into any userform.

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

In the last part, I take the collection of custom objects from Part III and write each object to a worksheet. For instance, I filled a collection with CSalesOrder objects and now I pass that collection to the following procedure. It accesses the properties of the CSalesOrder class to determine if that specific sales order belongs on the backorder report and writes certain columns to a table on the worksheet wshSO.

I can pass this same collection to the WriteRepairs procedure where I can get at the sales orders that have been designated as repairs.

At the end of the "write" procedures, I call the AdjustNames procedure, passing it an array of headings and the number of the last row to which I wrote data. This procedure adjusts the defined names on the worksheet, which have the same name as the heading, to encompass all of the data.

At this point, I have several worksheets of data that I can access with formulas and summarize however I wish.

Download QBMRP.zip.

<< Part III

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

In Part I, I listed the controlling procedure that does the Quickbooks related stuff and calls all the other sub procedures. In Part II, I listed the sub procedures that created each of the four queries. Now that the queries are set up, the controlling procedure sends the XML requests to Quickbooks and waits for responses. I takes about four minutes to run the whole thing and three-and-a-half minutes are waiting for Quickbooks to send back the data.

Once the responses are received, I loop through them and use a Select Case statement to handle each request separately. By handle, I mean store the data in such a way that I can use it. For each of the four responses, I created a class module. For instance, I have a CPurchaseOrder class module that will be used to hold the purchase order information.

Nothing too fancy there. I set up a Public variable for every "field" that I'm bringing over, then use MZTools to convert all those Public variables to Property Get/Let procedures. With the class module set up, I can parse the request and put all the data in the class module.

The appropriate IResponse object is passed into this function, which then returns a collection. The collection is a bunch of CPurchaseOrder objects and can be passed to procedures that do calculations or write to worksheets or both. For every field that I want to store in the class, I have to determine if Quickbooks returned anything by comparing it to Nothing, then call the GetValue method to set the class module's property.

In addition to CPurchaseOrder and GetPurchaseOrder(), I have the following class module/function pairs: CAssembly, GetAssembly; CRawMaterial, GetRawMaterial; CSalesOrder, GetSalesOrder.

I don't need to use class modules for this. That's probably true for most uses of class modules. But there are a few reasons why I think it makes sense here. I could loop through all of the responses and immediately write the data to a worksheet, since that's all I'm doing in the end anyway. With class modules I can access the Quickbooks data once, create multiple sub procedures without having a bunch of hard-to-maintain module-level variables, and easily add new calculations/output.

Accessing Quickbooks data is key because of the requirement to check the objects for Nothing and use the GetValue method to actually retrieve the data. If I, for instance, wanted to write a subset of my sales orders to a worksheet, I would have to limit them with code like this:

I have to make sure that IsFullyInvoiced, IsManuallyClosed, and CustomerRef were returned by Quickbooks before I can access their values. Once that test is passed, I have to make sure that FullName was returned. I can't check for FullName Is Nothing until I know that CustomerRef is there. Once I know that all the objects are there, I can GetValue to actually test my conditions.

With a class module, I test each of these conditions only one time, then put the data into the classes' property. Now I can access the classes' property without all the rigmarole. Compare:

So much cleaner and easier to read.

The classes also come in handy for splitting up my procedures. I could use module-level variables to pass the responses back and forth between the procedures, but it's easier and cleaner to pass everything in a collection. In order to put all that data into a collection, I create class modules which are held in the collection. I use sales orders to track product that I've loaned out to potential customers (kludgey workaround, thanks Intuit). I obviously also use sales orders to track sales that haven't shipped - their intended use. With all my sales orders in one class, I can call a WriteSalesOrders procedure and a WriteLoaners procedure and pass each the same collection of CSalesOrder objects.

Once I had this set up, I liked it so much that I decided to use sales orders to track product that needed repair. All the sales orders were already in the class, so I only had to write a WriteRepairs procedure to pull the appropriate sales orders out and stick them on a worksheet. That compartmentalization makes it easy to add and delete features without screwing up stuff you know already works.

<< Part II Part IV >>

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

In Part I, I listed the entry point procedure that controls the flow. Once the connection to Quickbooks is made, I create XML requests for each query I want to run. I'm using the QBFC object model so I don't have to put the XML together myself. For each query, I add the columns that I want. I found that bringing over the whole table takes significantly longer than if I limit the number of columns.

For the sales orders, I set the boolean to include line items. The line items are what contain the inventory item detail. Without the line items, I would just be getting sales order header type information which is of no use to me. The same goes for the purchase orders.

For the inventory (assemblies and raw materials), I filter the list to include only active inventory items. Items that aren't bought or sold have been made inactive and I don't need them. There are plenty of other filters that can be applied to inventory items, sales orders, or just about anything else you get from Quickbooks. The documentation for the SDK lists all the filters that can be applied. I could, for instance, only pull sales orders that were created in the last year.

To include columns, I "add" the field name to the IncludeRetElementList object. If I spell the field name wrong, it returns nothing; no error, it just ignores that column. Most fields are simple values, like TxnID is a String value. Some fields are structures within themselves. CustomerRef returns a reference to the customer from which you can get at other customer related properties. You can't pull in just the customer name, though, you have to pull the whole CustomerRef structure.

Here are the four procedures that set up the queries (XML requests) that will be sent to Quickbooks:

<< Part I Part III >>

Part I: Introduction and Controlling Procedure
Part II: Creating the Quickbooks Queries
Part III: Filling Class Modules
Part IV: Writing to Worksheets

The Quickbooks SDK is the only way I know of to get data out of Quickbook's proprietary database format and into Excel (SDK=Software Developer's Kit, if you didn't know). It's an XML based system; you send an XML request and it returns and XML response. Assuming it was able to fill your request, the XML response will be a structure filled with the data you requested.

There is a very, very thin object model wrapper that will take care of the XML for you. Thin though it may be, it's still better than creating my own XML strings. Inexplicably, everything in the OM is a method, that is, there are no properties. If you want to get the customer's name, it looks like

x = CustomerName.GetValue

rather than just

x = CustomerName.Value

or even

x = Customer.Name.

But that's not all. If there is no CustomerName object associated with your request, the CustomerName object will be Nothing and the GetValue method will fail. So now to get the customer's name, you need code like this:

For every "property", you need to test for the nothingness of object, then call it's GetValue method. It seems like it would have been just as easy to create a more robust object model that had real objects, properties, and methods. I would prefer a Customer object with a Name property rather than a CustomerName object. The result is that the code you need to write, compared to say using ADO to access a SQL database, is extremely long. The correct answer is for Intuit to provide an ODBC driver that accesses the data directly. Currently, there is a third party ODBC driver that uses the XML request/response system. In addition to long code, everything runs significantly slower than ADO.

I wanted to put some code out there for other people who may want to do the same things. I didn't want to spend all my time complaining about the SDK, although I can see that's unavoidable. In its defense, the SDK is extremely well documented. You may not like the way you have to do it, but you'll know how to do it. Also, the developer community is very active and helpful.

For my first installment, I'm posting my main procedure. There's a hyperlink on a worksheet that displays the last time this procedure was run. Because it takes more than five minutes to run, I don't want to run it if the information is fairly up to date. The FollowHyperlink event is used to run this procedure when the hyperlink is clicked.

The purpose of this workbook is to manage inventory. I need to have several tables of information to tell me how many pieces I have on hand, how many are out on loan, how many are in repair, how many are on order from vendors, and how many are due to customers. If Quickbooks had an "inventory location" feature, I wouldn't need any of this, but they don't.

The flow of the procedure starts with the standard Quickbooks stuff. I have to create a session, open a connection, begin the session, etc. Once I have a session up and going, I need to create the requests. For my purposes, I have four requests; sales orders, purchase orders, inventory items, and inventory assemblies. For each request, I create a AppendxxxxQuery, then pass it to a procedure that builds the query. I'll post that procedure in a future installment.

I end up with more tables in my workbook than requests because some of my tables are subsets of Quickbooks' tables. My inventory on loan, for instance, is recorded as a sales order to a dummy customer. I query all the sales orders from QB and pull out the loaners to create that table.

Once my queries are completed, I send the request to Quickbooks and get a response back. This takes the longest by far. There's no callback so that I can update a progress bar either. Because I sent four requests, I should get four responses. Each response is sent to a procedure that fills a collection with custom objects. The procedures and the class modules will come in a future post. Setting up the class modules is a pain, but the alternative is checking the stupid objects for Nothing and using GetValue to get the values. This way, I have an object called Assembly which has a number of properties like the one called QtyOnHand. I can get that value like I would a property of an object in Excel's object model.

Now that I have four collections filled with CAssembly, CPurchaseOrder, CRawMaterial, and CSalesOrder objects, I pass those collections to 'Write' procedures that loop through the objects and write tables to worksheets.

That's pretty much it. I have some other procedures that filter items out of these tables and I have a bunch of formulas that draw info out, but that's the meat of it. I'll post the other procedures and class modules soon. I don't know if I'll have a download associated with this. While I think it's nice to see everything in one place, it will hardly work with just an Quickbooks install. Here's the entry point code:

Part II >>