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.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
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
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
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