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.
