Quickbooks SDK Part I

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

rather than just

or even

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 a Quickbooks install. Here’s the entry point code:

Part II >>

Flashback

An article from PC Magazine describing that newcomer Microsoft Excel. A New Face for Spreadsheets.

List Price: $495
Requires: AT or 386-based PC, 640 K RAM, hard disk, color/graphics monitor, DOS 3.0 or later, EGA, expanded memory, and mouse recommended.
In Short: A powerful, full-featured, graphics-interface spreadsheet that could seriously threaten 1-2-3. Not copy protected.

Ah, memories.

Excel 2007 FAQ (Draft)

This is my first stab at a FAQ for Excel 2007. It’s based on Beta 2 Technical Refresh, so there is a chance that some of the problems noted will be fixed in the final version. Please feel free to add your own Q & A’s. Or, just the Q if you don’t have an A.

[Note: Updated Nov-6-2006 with some corrections.]

User Interface

Q: How do I open a file?

A: That round logo in the upper left corner is not just for cosmetics. It’s called the Office Menu button, and it’s used for a variety of purposes, including file operations. Click it.

Q. How do I get to the Excel Options dialog box?

A. Click the round Office Menu button, then click Excel Options.

Q. I clicked the round Office Menu button, but I don’t see Excel Options.

A. Look at the very bottom. It’s a button, not a menu item. And make sure you don’t click Exit Excel by mistake.

Q: how do I hide/show the Ribbon?

A: Use Ctrl-F1 to toggle the display of the ribbon.

Q: Where did the xxxxxx command go?

A: There’s a pretty good chance that it’s on the ribbon somewhere. But there’s also a chance that it’s not on the ribbon. In the latter case, you can add the command to your QAT. Also, try using the old Excel 2003 hot keys (for example, Alt+T, I to display the Add-Ins dialog box).

Q. What’s a QAT?

A. QAT is Quick Access Toolbar. This is the only user interface element that can be customized by the end user.

Q. Where are my old custom toolbars?

A. Click the Add-Ins tab and you’ll see them.

Q. I can’t make my old custom toolbars float.

A. No, you can’t.

Q. How do I “tear off” the Fill Color icon so I can float it?

A. You can’t.

Q. How do I get Help? The ‘ask a question’ box is gone.

A. Press F1, or click the little question mark icon in the title bar.

Q: How can I hide the status bar in Excel 2007?

A: You must use VBA to hide the status bar: Application.DisplayStatusBar = False

General

Q: I opened a workbook and my worksheets have only 65,536 rows.

A: Save it in an Excel 2007 format, close it, then re-open it.

Q: Where is the list with open workbooks?

A: Use View / Window / Switch Windows. Better yet, add this command it to your QAT. Right click on Switch Windows to add it so it is always one click to access it.

Q: Ctrl+A doesn’t select all of the cells in my worksheet.

That’s probably because the cell pointer is inside of a table. Press Ctrl+A three times to select all worksheet cells.

Q. The Custom Views command is grayed out.

A. That’s probably because your workbook contains a table. Convert the table to a range, and then you can use Custom Views.

Q: What happened to the ability to create a pivot table using the Multiple Consolidation Ranges option?

A: That option still exists, but you need to add the ‘PivotTable and PivotChart Wizard’ command to the Quick Access toolbar (Found in ‘Commands not in the Ribbon’), and use that command to start a new pivot table.

Q: I can’t find the command to apply names to cell references in a formula. In Excel 2003, the command was Insert / Name / Apply.

A: The Define Name control in the Formulas / Defined Names groups is a drop-down. Click the down-arrow, and you’ll see the Apply Names command.

Q: Why doesn’t the F4 function key repeat all of my operations? 

A: I don’t know. The very useful F4 is much less useful in Excel 2007.

Q. What happened to the ability to “speak” the cell contents?

A. To use those commands, you must customize your QAT. They are listed under ‘Commands Not in the Ribbon’.

Q: Where is the Mail Recipient (body) option in Excel 2007?

A: You must customize your QAT. They are listed under ‘Commands Not in the Ribbon’

Formatting and Printing

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That’s probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the ‘Display options for this worksheet’ section, and remove the checkmark from ‘Show Page Breaks’.

Q: Can I add that ‘Show Page Breaks’ option to my QAT?

A: No. For some reason, this very useful command isn’t available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can’t find a way to get the orientation back to normal. There’s no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I’m trying to apply a table style to a table, but it has no effect.

That’s probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can’t find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Charts and Graphics

Q: Double-clicking on a chart element doesn’t display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it’s very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it’s always visible.

Q. I added the Chart Elements control to my QAT, but the original control no longer shows added elements such as trendlines and error bars.

A. Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What’s a dialog box launcher?

A: It’s the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I’m working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title ‘Format Shape,’ and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn’t it? When you’re finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don’t work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. I added a few shapes to a Chart sheet. Those shapes don’t get changed when I apply a new document theme.

A: Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose ‘All Commands’ and then select ‘Change Shape’. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can’t I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment’s border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don’t. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to “Set as Default Shape.” This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you’ve applied to the shape as the default (fill, outline, effects). The command should probably read “Set as Default Shape Formatting.”

Macros

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don’t have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable ‘Show Developer tab in the Ribbon.’

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don’t accept the default “Yes” button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I’m trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can’t. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you’re a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys “%w{F6}”

Q: I’m trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the “Copy xxxxxxxx” option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp “HP10062493”. The text in quotes is the topic ID pasted from the clipboard.

Two website additions: Properties in a standard module and a chapter on Names and Formula formatting

In
Property procedures in a standard module
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba-property_procs_in_std_module.htm
I show that it is possible to declare a Property in a standard module and briefly discuss the pros and cons with doing so. This tip results from something I was doing some months ago. In the midst of coding — I’ve long since forgotten what — it struck me was that, if I were writing a class module, what I was doing was ideally suited to being made a property. So, I tried it in a standard module in Excel 2003 and, lo and behold, it worked.

Names and formatted formulas
http://www.tushar-mehta.com/publish_train/data_analysis/08.htm
is a draft chapter in a book. It looks at the use of names referencing references using absolute address as well as relative addresses as well named constants and formulas. Still to come is the use of names that refer to the current sheet. The draft chapter also looks at formatting a formula using line breaks and spaces, both of which are “white space” that Excel ignores. I vaguely recall from several years ago that I did something that caused Excel to collapse all the pretty formatting in a cell but I cannot recall what it was.

SaveAs in Excel 2007

Hi all

Creating a new workbook from the active sheet is not so easy anymore now we
have Excel 2007 with so many different file formats.

Below is a code example to copy the active sheet to a new workbook and save it.
This example is working in 97/2007.

If you run the code in Excel 2007 it will look at the FileFormat of the parent workbook and save the new file in that format.
Only if the parent workbook is an xlsm file and if there is no code in the new workbook it will save the new file as xlsx,
If the parent workbook is not an xlsx, xlsm, or xls then it will be saved as xlsb.

This are the main formats in Excel 2007 :

51 = xlOpenXMLWorkbook (without macro’s in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)

If you always want to save in a certain format you can replace this part of the macro

Select Case Sourcewb.FileFormat
    Case 51: FileExtStr = “.xlsx”: FileFormatNum = 51
    Case 52:
        If .HasVBProject Then
            FileExtStr = “.xlsm”: FileFormatNum = 52
        Else
            FileExtStr = “.xlsx”: FileFormatNum = 51
        End If
    Case 56: FileExtStr = “.xls”: FileFormatNum = 56
    Case Else: FileExtStr = “.xlsb”: FileFormatNum = 50
End Select

With one of the one liners from this list

FileExtStr = “.xlsb”: FileFormatNum = 50
FileExtStr = “.xlsx”: FileFormatNum = 51
FileExtStr = “.xlsm”: FileFormatNum = 52
FileExtStr = “.xls”: FileFormatNum = 56

Or maye you want to save the one sheet workbook to csv, txt ot prn.
(you can use this also if you run it in 97-2003)

FileExtStr = “.csv”: FileFormatNum = 6
FileExtStr = “.txt”: FileFormatNum = -4158
FileExtStr = “.prn”: FileFormatNum = 36

Sub Copy_ActiveSheet()
‘Working in Excel 97-2007
   Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    Set Sourcewb = ActiveWorkbook
 
    ‘Copy the sheet to a new workbook
   ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
 
    ‘Determine the Excel version and file extension/format
   With Destwb
        If Val(Application.Version) < 12 Then
            ‘You use Excel 97-2003
           FileExtStr = “.xls”: FileFormatNum = -4143
        Else
            ‘You use Excel 2007
           ‘We exit the sub when your answer is NO in the security dialog that you only
           ‘see  when you copy a sheet from a xlsm file with macro’s disabled.
           If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox “Your answer is NO in the security dialog”
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = “.xlsx”: FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = “.xlsm”: FileFormatNum = 52
                    Else
                        FileExtStr = “.xlsx”: FileFormatNum = 51
                    End If
                Case 56: FileExtStr = “.xls”: FileFormatNum = 56
                Case Else: FileExtStr = “.xlsb”: FileFormatNum = 50
                End Select
            End If
        End If
    End With
 
    ‘    ‘Change all cells in the worksheet to values if you want
   ‘    With Destwb.Sheets(1).UsedRange
   ‘        .Cells.Copy
   ‘        .Cells.PasteSpecial xlPasteValues
   ‘        .Cells(1).Select
   ‘    End With
   ‘    Application.CutCopyMode = False

    ‘Save the new workbook and close it
   TempFilePath = Application.DefaultFilePath & “”
    TempFileName = “Part of “ & Sourcewb.Name & ” “ & Format(Now, “dd-mmm-yy h-mm-ss”)
 
    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        .Close SaveChanges:=False
    End With
 
    MsgBox “You can find the new file in “ & Application.DefaultFilePath
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Also if you use code like GetSaveAsFilename then you must add a lot of code.

See the second example on this page
http://www.rondebruin.nl/saveas.htm


PDF in Excel 2007

Hi all

Excel 2007 B2TR and the final version will not have SaveAs PDF by Default.
You must install a MS ADD-in if you want to have this option in Office.

I am really glad the MS have added this in Excel 2007.
The 2007 version of my SendMail add-in will have this option also.

On this page there are a few vba examples and the link to the add-in so you can try it.
http://www.rondebruin.nl/pdf.htm

Have fun

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Weekend Forum

I got my tablet PC this week. It looks like an excellent machine. If only the carrier hadn’t dropped it and broke the screen and some other stuff. I’m fairly confident the carrier will do the right thing. Ed Bott, who sold it to me, has already refunded my money. And yet, I’m still depressed. This was a perfectly good tablet and I was going to give it a perfectly good home. Now it’s worthless and who knows how long it will be before I find a deal that entices me into a tablet. I was looking forward to using it on my way out to Eugene next weekend. I sure would like to have one before the MVP Summit next year. Oh well.

I downloaded, and am using, Firefox 2.0 this week. I like the built in spell checker. I thought someone said there was tab-close-undo that would be built into this version, but I don’t see it. I had a couple of problems with the new version. It seems to hog the resources on occasion, but I can’t pinpoint why. Also it didn’t save my preferences, particularly in the Tabbrowser Preferences add-on. Overall, it seems to work nicely.

This week’s question comes from Mike in Beantown:

I’ve been trying to create some reports using a Pivot Table and it’s “Calculated Field” functionality. There is a huge drawback to these, however, since I can’t figure out how to reference the column or row totals. For example, there is a way to show a column as “%of Total” or “%of column”, but this functionality isn’t available inside the formula space for the “Calculated Field”. What if one wanted to utilize these totals in a formula generation. For example, a running weighted average, or a transformation to a %of column or something.

If you’re going to a Halloween party this weekend, what are you going as?

Exporting All Graphics

One way to export all graphic images from a workbook is to save the file in HTML format. This creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and range images (created with the “camera” tool).

Here’s a VBA procedure that automates the process. It works with the active workbook.

Sub SaveAllGraphics()
    Dim FileName As String
    Dim TempName As String
    Dim DirName As String
    Dim gFile As String
   
    FileName = ActiveWorkbook.FullName
    TempName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name & “graphics.htm”
    DirName = Left(TempName, Len(TempName) – 4) & “_files”
   
‘   Save active workbookbook as HTML, then reopen original
   ActiveWorkbook.Save
    ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Workbooks.Open FileName
   
‘   Delete the HTML file
   Kill TempName
   
‘   If Excel 2007, delete all but *.PNG files in the HTML folder
   If Val(Application.Version) >= 12 Then
        gFile = Dir(DirName & “*.*”)
        Do While gFile <> “”
            If Right(gFile, 3) <> “png” Then Kill DirName & “” & gFile
            gFile = Dir
        Loop
    End If
‘   Show the exported graphics folder
   Shell “explorer.exe “ & DirName, vbNormalFocus
End Sub

It starts by saving the active workbook. Then it saves the workbook as an HTML file, closes the file, and re-opens the original workbook. Next, it deletes the HTML file because we’re just interested in the folder that it creates (that’s where the images are). If you’re using Excel 2007, the code loops through the folder and deletes everything except the PNG files. Previous versions use a combination of GIF and PNG files, so this step is skipped if you’re using a pre-Excel 2007 version. Finally, it uses the Shell function to display the folder.

You’ll find that the quality of the images is much better in Excel 2007. But, unfortunately, pictures of ranges still look terrible.