Using VBA to create import files for QuickBooks

A couple of years ago I created some spreadsheets to help me migrate data into QuickBooks. I set up a worksheet that followed the IIF format and then saved it as a tab delimited file. The biggest problem was that one row of data required three rows in the IIF format. If you try and use copy and paste method to adding new rows, this can become quite a pain. The addressing is all screwed up. I figured out how to do it with some Excel functions(e.g. rows and offset) but the final result was still troublesome.

I finally bit the bullet and decided to use VBA to dynamically create a new worksheet in IIF format. I select the rows I am interested in and then I let the macro iterate through the selection creating IIF transactions in the worksheet. After I did a lot of searching for VBA examples I had a macro that worked remarkably well. It is a very simple program but it took me a while to find the right commands. Although the macro was a success, I still had problems with the IIF invoice transaction was not doing what I expected it to do when QuickBooks imported it. It was “in” but I had to fix a lot of transactions. This was hardly the time saver I hoped for.

My next step in the evolutionary process was to switch from IIF to an AutoIT script. I chose to modify the VBA macro to write out the data as an AutoIT array. This allowed me to use an include statement in my main AutoIT script to include the file containing the AutoIT code for the array. Since I run the script interactively, this was an easy way to get the data into the program. The good news is that after fine tuning the script I was able to achieve a pretty high success rate with the invoices. Part of the problem was with the data but a more challenging problem was the timing issues. Interactive scripting is still closer to an art form than a science. The script is not perfect but it is an improvement. Another advantage of using AutoIT is that I was able to further extend the scripting process to  let it partially fill out the QuickBooks Receive Payments screen. The script would wait for me to manually match invoices before continuing.  QuickBooks does not have an IIF transaction for receive payments.Since this traditionally is a manual process in QuickBooks, this is a big time saver.

Today I wrote another macro using my previous work on the macros as my example. Within a fairly short period of time I was able to create a new VBA macro to write out a worksheet of bills in the IIF format. I could of chosen to write an AutoIT script but I remembering having good success with importing IIF bills. Once again this will save me a lot of manual data entry and will undoubtedly make the bills more accurate since it is important that the memo fields get filled out with the proper billing information.

Importing invoices into QuickBooks using AutoIt Script

For several years I have successfully used the QuickBooks IIF format to import bills, checks, and employee hours. I have been less successful with invoices and I was unable to import payments. Several times I considered writing a C# or VB program to import the data but I backed off because it looked like it would take too much time. I looked at AutoIt but felt it would have a steep learning curve, too. Yesterday I decided to give AutoIt a try and after a couple of hours I was able import both invoices and payments.

Since I am the treasurer for a Habitat for Humanity affiliate I have a strong need to make sure our mortgage tracking system is kept in sync with QuickBooks accounts. I use an Excel spreadsheet to reconcile mortgage payments we record in the specialized system with the actual bank deposits. This is where I find the errors since we use one person to record the payments and another person to record the deposits. For each mortgage payment I must allocate it to principal, escrow, and late fee. If everything works fine, then both systems have the correct values for each individual home owner’s mortgage and escrow accounts. I set up an invoice for the principal owed when the home owner closes on the house. Every month I enter invoices for the escrow and late fees due. Habitat does not charge interest. On an average month I need to enter 30 to 40 invoices and 30 to 40 payments. As you can see, I have a vested interest in seeing that this project be quick and accurate.

The present script is a work in progress. I feed the data into the script via a VBA macro I created in my spreadsheet. The macro writes out the payment values as AutoIt code onto a new worksheet. Then I paste this code into my AutoIt script, save the script, and run it. Crude but effective. The code for the invoice screen was pretty straightforward and I am letting it run automatically. The code for the payment requires human interaction since I must allocate the payment to the different invoices. It pre-fills the payment screen and then asks me to allocate the payment and save the payment before proceeding. I click on a popup to process the next payment.

Before next month I will have the macro writing out the code to a text file that I can “include” in the script and include a little bit more error checking.