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.
Related posts:
Related posts brought to you by Yet Another Related Posts Plugin.
3 responses so far ↓
1 Robert // Sep 18, 2008 at 10:33 am
This is really cool. I’ve thought about using AutoIt for awhile now as there are some things that I cannot import into quickbooks using QODBC.
Would it be possible to get a copy of your script?
Perhaps you could post it to your site?
Cheers, Robert
2 Angela Atwood // Dec 12, 2008 at 1:54 am
Thank you very much for this information! I will look on your site for your script.
href=”http://www.infinitelyvirtual.com/categories/applications/quickbooks-terminal-server.html”>InfinitelyVirtual.com
3 Alf Chi // May 29, 2009 at 2:27 pm
hello Mr. William Huber, am interesting in the macro and autoit script that you have used to import your invoices from excel to quickbooks, how would i be able to get a copy of this script & macro?
thanks in advance
Leave a Comment