Category: QuickBooks

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.

Aaron Stebner’s WebLog : Automated cleanup tool to remove the .NET Framework 1.0, 1.1, 2.0, 3.0 and 3.5

A big thank you goes out Aaron for his automated cleanup tool to remove the .NET Framework 1.0, 1.1, 2.0, 3.0 and 3.5. Yesterday I found myself in “No Man’s Land”. My PC was trying to install Microsoft .NET Framework 2.0 Service Pack 1(KB110806). It did not work and complained about not finding 2.0a.  The .NET Framework 2.0 that worked the day before was now toast. Live Writer and QuickBooks 2008 no longer worked since they depend on .NET. I get a little antsy when I have to resort to my backup plan to run payroll. Uninstalling .NET via the control panel did not work. It could not find the source!? Running the dotnet.exe I originally used to install .NET Framework 2.0 did not work either. So I ran Aaron’s tool to uninstall .NET Framework 2.0. After I rebooted I let Microsoft Update install a new version of .NET Framework 2.0. Live Writer and QuickBooks 2008 are working again! Yea, Aaron!

For those who are still trying to figure out what I am talking about Aaron describes his tool as:

This tool automates the manual cleanup steps for the .NET Framework 2.0 that I posted a while ago.  These steps have helped solve most of the known .NET Framework 2.0 beta uninstall issues that I know of.  In addition, the tool can be useful to return your system to a known (relatively clean) state in case you run into any .NET Framework 2.0 installation failures so that you can try to install again.

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.

Problems importing IIF transactions into QB2004

A while back I wrote a post called, “Update to Excel Macro to import IIF transactions“. In it I talked about a macro I was working on to import IIF transactions into QuickBooks. I had intended to share the macro once I got finished testing it. Well, I have finished testing it. There is good news and bad news. The good news is that the macro works as intended. The bad news is that although the transactions will import into QuickBooks, QB2004 does something funny with the transactions and I have not figured why. As an example if I import a late fee invoice, the late fee does not show up on the income statement even though I can see and edit the invoice. If I delete the line on the invoice and re-enter the line, the income will appear on the income statement. Obviously this is not the answer I was looking for.

Since then I have downloaded the SDK and am looking at modifying one of their sample programs to be a import program. With my workload this problem has not been high on my priority list. For whatever its worth, here is the macro as a text file.

Create IIF Rows from selection

Free Download of Microsoft Office Accounting Express 2007

Office Accounting 2007

Microsoft Introduces New Accounting Software and Services for Small and Home-Based Businesses

Microsoft Corp. announced the availability of Microsoft® Office Accounting Express 2007, financial management software designed for early startups and home-based businesses that currently use pen and calculator or spreadsheets to run their operations. Office Accounting Express 2007 consists of desktop software available as a free download and seven integrated online services.

Thank you and have a wonderful day,

Eric Ligman

Microsoft Senior Manager

Small Business Community Engagement

This posting is provided “AS IS” with no warranties, and confers no rights

Link to Free Download of Microsoft Office Accounting Express 2007

I downloaded this program and installed it on my Vista RC1 virtual machine. RC1 is my beta box. This program is a QuickBooks alternative so I should check it out. The installation program complained and said I needed to install SQL 20005 SP2 to avoid problems. They talk about SP2 but it is not available. Tomorrow I will import a simple QuickBooks company file and see what gets screwed up. Imports are typically pretty ugly. I thought I saw someplace a better time billing feature using the Outlook calendar.

Getting QuickBooks to run as non admin on a stand alone workstation setup

With a shout out to Mike in the blog comments… he said “I was troubleshooting an installation of QB07 on a 2003 Terminal Server. I had all the security modifications that I have used for the previous versions and it still wouldn’t work. What I discovered is that normal users don’t have but need rights to: Documents and Settings\All Users\Application Data\Common Files\Intuit\QuickBooks\qbregistration.dat I added rights to that file and the program ran properly. Bad news about having to be logged in at the server for the Database Manager. When I called support before installing on our server they never mentioned that to me. I was initially happy they had come up with a solution that didn’t require running full QuickBooks on my server. For the 2006 version I had installed QB running the Database piece on a different computer with a drive mapped to the server and it work fine for us. I may have to uninstall the 07 version and try that trick again.”

I originally could not get QuickBooks 2007 to run without admin rights on a stand alone machine… one I gave “Users” Full control to Documents and Settings\All Users\Application Data\Common Files\Intuit\QuickBooks\qbregistration.dat just like Mike said… it worked like a champ.

The official way to do it from the Intuit folks is to flip it so that the QB runs as a “service”

http://www.threatcode.com/quickbooks_2007.htm

Look at the screen shots here and do that extra step of changing the permissions of the “dat” file.

Share this post: email it! | bookmark it! | digg it! | live it!

Link to Getting Quickbooks to run as non admin on a stand alone workstation setup

–> I blogged this to keep track of the best way to install QB07 and run it as a normal user until Intuit updates their documentation.

Mozilla – Home of the Firefox web browser and Thunderbird email client

Mozilla – Home of the Firefox web browser and Thunderbird email client

The number one reason for me to update to version 2 of Firefox is spell check. This should improve my blog writing throughout the internet. It will probably be some time for me before I update IE6  to IE7. I am still using QuickBooks 2004 in several places and it requests that for a continued happy experience with QuickBooks I should not upgrade to IE7 until I upgrade to QuickBooks 2007.

Update to Excel Macro to import IIF transactions

 

A while back I wrote a new macro to import IIF transactions, New Excel Macro to import IIF transactions. Last week I put it to the test and found several bugs. All of the bugs were minor but annoying. They created a lot more work for me but I am hopeful the next time I test this macro, it will save me time. Most of my bugs were spelling issues. I used different spellings on one customer and I forgot to include the group item. Naturally the spellings are very important if you want the invoices to end up with the right customer and account. The sneakiest bug was the one I got when I started a row with TRN rather than TRNS. QuickBooks complained about an out of place SPL row rather than pointing out my transaction should have started with TRNS rather than TRN.

New Excel Macro to import IIF transactions

I got an email recently from someone who was trying to import QuickBooks IIF transactions. Although I could not help this person with the problem, I did get motivated to write an Excel macro to import IIF transactions. One of the more tedious tasks I do as Treasurer for our Habitat affiliate is inputting payment data for our home owners. Each payment is split into three parts, principal, escrow, and late fee. The actual breakout of the payment is determined by our mortgage committee when they enter the data into their mortgage tracking program. This is important financial data so I keep track of the data in QuickBooks, too. Every month I use a spreadsheet to reconcile the deposit journal with the payments recorded into the mortgage system to make sure we do not miss anything. Then I get to the tedious part. I have to enter the payment data for each homeowner into QuickBooks before I can calculate how much to transfer into the escrow checking account. The good news is that we “only” have 37 home owners. The bad news is that I have to make two or more transactions per homeowner payment. This is the most time consuming portion of my Treasurer’s tasks.

My partial solution to this problem is to create about half of the transactions, escrow and late fees, directly from the spreadsheet. In my case I enter QuickBooks invoices for every escrow and late fee payment. The problem is that the escrow and late fees payments change every month. Another problem is that you need to create three rows in an IIF for every payment. In the spreadsheet I use to reconcile the deposits, I use one row per payment. So here is how I solved the problem.

I select the rows I want to generate invoices for and then run an Excel macro that creates a new worksheet in the IIF format with the data from the payments. Then I save the new worksheet as a Tab delimited file with an iif extension and import it into QuickBooks. The logic is pretty simple.

  1. You create a new Export worksheet. You delete the old Export worksheet if it exists.
  2. For every row in the selection you check to see if was part of a deposit. My worksheet has all of the home owners listed. If a home owner does not make their monthly payment, this cell is empty. If a home owner makes more than one payment in a month I will have two or more rows for them.
  3. If it was part of a deposit and the escrow portion is greater than zero, you make an escrow invoice.
  4. If it was part of a deposit and the late payment is greater than zero, you make an late fee invoice.

So there it is. Since I track escrow liabilities by home owner, each home owner has their own QuickBooks “Item” that points to their escrow account. I used the “vlookup” command to fill out the invoice with the correct QuickBooks “Item” for each home owner.

The other half of the transactions is the data entered on the Customer Payment screen. Although there is an IIF transaction for customer payment it does not help me. The Bill Payment transaction is not helpful, too. It would be nice to automate this but I will settle for my small improvement in the process.

Excel Tip: Converting CSV files into IIF files

The problem with importing data into QuickBooks is their funky multi-line IIF file format. For each transaction(e.g. invoice) in the single row CSV format you need to create three or more rows in IIF format. For me I import the CSV file into one worksheet and create a separate worksheet I call IIF for the IIF transactions. You could code up each cell in the Export individually but I like the cut and paste method for speed and to cut down on errors. They key bit of information for me was learning about the ROW and OFFSET functions in Excel. As an example I have used the following calculation on my IIF worksheet to copy the contents from a CSV row.

OFFSET('February 2006'!$C$2,((ROW()-1)/3)-1,0)

Using C2 as my starting point in the CSV worksheet(i.e. February 2006) and three rows for each IIF transaction in IIF worksheet, I can copy and paste the three rows that make up the IIF transaction to create more IIF transactions in the IIF worksheet. In this example the next IIF transaction will point to C3 in the CSV worksheet without manually editing.

The final trick is save the Excel file once in Excel format and then save the IIF worksheet in “Text (Tab delimited)” format with a file name like, export.iif. When you go into QuickBooks you will now be able to see file in the Import menu. If you have set up your IIF worksheet with the correct values for the Account, Item, etc. you should be able to import the IIF transactions easily and accurately into the correct places.

Live Meeting on Microsoft Small Business Accounting

This week I sat in on a Small Business Accounting webcast using Live Meeting. Participating in the using Live Meeting was pretty cool! The combination of sound, slides, and chat facilities to ask questions worked for me. I cannot say I paid 100% attention but I did get enough out of the presentation to get a much better idea of why some QuickBooks users will want to wait. I am a QuickBooks Payroll DIY’er. I am not sure Microsoft’s spreadsheet solution is where I want to go.

The MSDE solution used by SBA is probably a more elegant solution than QuickBooks 2006 solution. Sometime this quarter I will check SBA out but I am a little busy right now.

RE: Network install instructions for QB 2006

When using QuickBooks 2006 or QuickBooks Enterprise Solutions 6.0 in a multi-user environment, *the one best thing to know is that the new QuickBooks database requires new installation procedures with all machines running on Release 3 or above.

To help you support your clients with QuickBooks 2006 in a multi-user environment, Intuit offers:

* White Paper on QuickBooks 2006 in Networked Environments
PDF file (313KB)
* Master Tip: Installing QuickBooks 2006 in a Networked Environment
* KBID on Troubleshooting Error (-6177,-0)
* Special for Users of Enterprise Solutions 6.0
See article below.

*Caution.* Please do not attempt to Upgrade*QuickBooks, *Rebuild* a data file, or *Clean Up Company File** across a network. If you have clients in a networked environment, please work with them to make sure they avoid such actions.

The caution holds true * whenever the file is being accessed from a remote drive. QuickBooks never supported major file operations across a network in earlier versions and does not support them in QuickBooks 2006.

Our technical support center has recently seen a spike in calls generated by this problem. For background on the issue, and instructions on how to proceed, please read KBID 118556, “Rebuild, Update, or Condense Takes an Exceptionally Long Time or Does Not Complete

*Warnings to Be Added in Future Release of QuickBooks.* Intuit will be adding a warning message in a future release to help avoid the problem. Then, when the software senses that a major file operation is being attempted across a network, the user will be warned first. (The warning was in earlier versions of QuickBooks and was omitted from QuickBooks 2006 in error.)

** Condense vs. Clean Up Company File.* What was called “Condense” in QuickBooks 2005 and earlier is now “Clean Up Company File” in QuickBooks 2006. In 2006, QuickBooks is using a new database structure for improved operations; however, the overall size of the file does not reduce significantly during cleanup. While old data is removed, the overall structure retains much of the same footprint, and therefore the action was renamed.

Release 3 (R3) addresses several rare issues that could be troublesome should they occur. We recommend you confirm that your clients have upgraded their systems to R3.

Specific R3 modifications include:

* The “*Connection Has Been Lost [99937]*” error message related to
network installation has been updated. The occurrence of such
“Connection Lost” messages should be greatly reduced.
* *Error 1911 *(when desktop icons no longer work after an
unsuccessful attempt to install) *has been eliminated in the
current release*. This situation is triggered when an installation
of QuickBooks 2006 is attempted on top of a pre-existing
corruption in the user’s Windows system registry. While the
problem is not unique to QuickBooks, a fix in R3 removes the
potential that the error would arise in future installations.
*Note:* If you or a client should experience this issue with a
previous release, Intuit will help you resolve the issue.
* C=224 errors should no longer appear when making a backup.
* Icons listed in the Navigation portion of the Icon bar *can be
removed or edited*.

For a complete list of the changes to QuickBooks included in the R3 update, see the release notes

*Note:* Release 4 (R4) is not meant for general use. R4 includes all issues addressed in R3 but makes an additional adjustment to work with one third-party application (ReportWiz). The vendor is separately alerting its customers on how to obtain R4. Other QuickBooks users on R3 don’t need R4.

*Changes to Install Process: Read before You Install. *Because of the new database across QuickBooks 2006 and Enterprise Solutions 6.0, the install process in a multi-user environment will differ from previous installations. These issues are especially important when dealing with QuickBooks Enterprise Solutions 6.0.

Bottom line… need to mess with that data file? Drag it to a local drive for maintenance…fun huh?

[Via E-Bitz – SBS MVP the Official Blog of the SBS “Diva”]

Top QuickBooks Support Issues – Restricted Access to Windows User Account

Top QuickBooks Support Issues – Restricted Access to Windows User Account
Your user account for Windows was created with Restricted access to system resources. This will prevent QuickBooks from operating properly. Please contact your system administrator and ask him or her to grant you Standard user rights.

The easiest way to fix this problem is to go into computer management on the local computer at the workstation or by using computer management on another computer with administrative priveleges and using the connect to another computer feature under Action and add the person(domain\username) to Power User group. I decided to post this so I can find this info quickly.

Local Admin/Power User Hall of Shame

Local Admin/Power User Hall of Shame

This site contains a partial list of programs that require Admin rights and do not support current security patches. The SBS Diva was writing about a vendor whose program support would not support users who had installed the current set of XP security patches.

It also has instructions on how to use QuickBooks with a restricted user. I will have to try it out although I am uncomfortable with an non Intuit solution.

Excel Round time to the nearest quarter hour

Excel Round time to the nearest quarter hour

I screwed up with my paysheet calculation for one of our employees on our farm for the last pay period. I was calculating the number of hours worked in my head. One of our employees pointed out the mistake and I decided to minimize future mistakes by letting Excel do the math for me. The hard part was rounding to the nearest quarter of an hour till I found this link. Thanks Mr. Excel!

I guess the next thing to do is to create an IIF file to import into QuickBooks.

The business of trust

The business of trust

So what are some issues small business owners should be concerned about when they let someone else do the bookkeeping?

If you answer “yes” to any of these, you may have issues in segregation of duties:

  1. Is the person who handles your cash also responsible for recording the cash?
  2. Does the person who pays or orders inventory also receive the materials?
  3. Are two or fewer people responsible for the accounting function?
  4. Is only one person responsible for reviewing financial statements each month?
  5. Is your review of financial journals sporadic?

If you answer “no” to any of these, you may have issues with Bank Reconciliation:

  1. Do you review canceled checks and endorsements on a monthly basis?
  2. Do you compare payroll checks with your current employee records?
  3. Do you question funds transferred between bank accounts?
  4. Do you track the number of credit card bills you sign per month?
  5. Are bank reconciliations performed on a timely basis?
  6. Is someone responsible for reviewing the reconciliations each month?
  7. Do you verify reconciled items?

If you answer “yes” to any of these, you may have issues with documentation:

  1. Do you ever sign blank checks?
  2. Do you ever sign checks without original supporting documentation?
  3. Do you ever sign checks without canceling supporting documentation?
  4. Have funds ever been transferred between accounts without review or verification?
  5. Do you ever sign checks for new business vendors without knowing or verifying their name and association with your company?

If you answer “yes” to any of these you may have issues with employees:

  1. Are any of your employees extremely possessive of their work records and reluctant to share their tasks?
  2. Are any of your employees apprehensive about vacations and time off, while always being the first in the office and the last out?
  3. Have you noticed a substantial change of lifestyle in any of your employees?
  4. Do any of your employees have a possible substance abuse problem?
  5. Are any of your employees living beyond their means?
  6. Have you ever hired an employee before checking references?
  7. Do you permit your accounting personnel to work longer than a year without taking a vacation?
  8. Do you have any accounting staff or key personnel who have not been secured with a fidelity bond?

If you answer “no” to any of these you may have a problem with assets:

  1. Are blank check stocks and signature stamps safely secured?
  2. Do you restrictively endorse all checks when received?
  3. Do you deposit cash and checks daily?
  4. Do you maintain a list of office furniture, equipment, and company vehicles?

This is a good checklist for a small business and for a non-profit to consider. The biggest consideration for not implementing any of these recommendations is the financial and operational risk. As an example, our Habitat affiliate has a pickup truck that we carry at salvage value. The financial risk is minimal. It is convenient to have available but it is not going to stop construction if it is stolen or broken down. As a result our bookeeping of the truck is minimal. However, many of the other recommendations require us to just work smarter!

Importing a lot of transactions into QuickBooks

I have been working hard at finalizing the 2003-2004 financial statements for our Habitat affiliate. I am the treasurer now and I need to file the 2003-2004 Form 990. The problem is that the final financial reports coming out of QuickBooks were not to my liking. The previous treasurers/accounting firms appear to have used a combination of Peachtree, Excel, and other data to prepare the statements. I have moved the data into Quickbooks last year. I wanted all of the data I was going to report to the IRS to be in QuickBooks. The combination of several treasurers and the lack of original cost data to support some of our transactions made the going slow. Don’t get me wrong! We have lots of paper trails for cash donations and our payments to construction vendors. We were missing the cost of donated securities and donated land. I also found the mortgage information on the foreclosed property we sold missing. Finally the mortgage and escrow data conflicted with our mortgage tracking system.

Probably the most irksome problem was reconciling our mortgage and escrow tracking system, Habitrax, with our accounting data. The first part of my solution was to create a spreadsheet to track deposits and split out each homeowner deposit into principal, escrow, and late payment. We generally have three to four bank deposits a month so I summarize the data based on bank deposit at the bottom of the page. This made it nice at reconciling the checking account but I still had problems reconciling QuickBooks and Habitrax. My most recent change to the spreadsheet was to add reconcilition data to the bottom of the spreadsheet. I use a Habitrax report to subtract out the deposits received last month but deposited this month and add in the deposits we have recorded as received but have not deposited. In a perfect world the resulting number will match the value on the Habitrax report. In my world I have another row for missing receipts, deposits, errors, and Habitrax only transactions(e.g. principal to escrow transactions). This allowed me to reconcile our accounting data to Habitrax data back to 2003. No one ever attempted to reconcile the two systems till I asked the question. Since reconciling these two systems by hand is practically impossible, errors have not been caught.

Obviously the two systems come up with different amounts. The difference is significant but not large. So the question is who is right. To reconcile the two systems I needed better information on the accounting side. The big change on the accounting side was to move away from the summarized transactions back to individual transactions per homeowner. To achieve this I entered an invoice for each homeowner’s mortgage. Each month I enter in a sales receipt for the individual escrows and late payments. Then I use their payment to pay the sales receipts with the rest going to pay the mortage balance. Now I had individual account balances for the mortgage and escrow for each homeowner and could reconcile individually.

To make this all come together I had to add adjusting journal entries to get the right historical balance. I added a column to the existing worksheets showing the receipt date and created a new worksheet that built import transactions in IIF file format. Since my source data was organized as one month per worksheet, I was going to create a batch of IIF transactions by month. I used data validation and the VLOOKUP function to minimze my data entry errors. Each IIF transaction requires three rows for each row of source data. So I used the OFFSET function so that I could copy the three rows and only move down one row in the source data. To extend the worksheet all I had to do was copy and paste. To shorten the spreadsheet all I had to do was delete the extra rows. After I had reviewed the sheet and made any adjustments, I saved the spreadsheet file. Then I saved the spreadsheet in Tab-delimited format. Since Tab-delimited format only saves the active worksheet this was perfect for my use. It created a Tab-dilimited format file that matches up exactly with the IIF format required by QuickBooks import function. I created separate files for each month. I entered a year and a half of data this way(75 transactions/month). Wow! I am finally ready to review the reports and file the 990.

A good use for Sharepoint companyweb

Yesterday I stored a QuickBooks backup for our Habitat affiliate on my internal companyweb website. I then went into the office and downloaded the file using the secure version of the website that is accessible from the internet. I restored the company file, made my changes, backed up the company file, and uploaded the file to the secure site. I have been making a lot of changes so I can finalize my monthly reports. The changes to the QuickBooks reports are interesting but I digress. I have been able to get more work done by working from home. I thought about using a USB flash drive but Yahoo Briefcase was pretty easy. The drawbacks is that Yahoo Briefcase is not as secure as my website and it has an upload limit. Now to check out webfolder encryption.

Fixing QuickBooks CoCreateInstance error 80040154

Yesterday I removed a few programs I was not using and ran Spybot to check and clean my PC. Somehow I broke QB 2004 in the process. When I tried to open it I got this message:

an unrecoverable error has occoured exception MSXML could not CoCreateInstance HRESULT=80040154 thrown with error code-2147221164

Function: ADR::Repository DoInitialLoad
File: ..srcRepository.cpp
Line: 81

After some investigation I found out that I was getting a non-serious error with IE about “cannot read properties at this time” and the Windows Update service was failing with a 0x800A01AD. After some research and some failed guesses I fixed the problem by re-registering msxml3.dll with the following command, "regsvr32 msxml3.dll". This must have been inadvertently been un-registered when I uninstalled Salesforce.com’s sync software.

Loading bills into QuickBooks

I am finally at the end of the project to transfer the accounting data from Peachtree to QuickBooks. The biggest change was to change the account and job names. I opted to follow the recommendations of Intuit and others to use the Unified Chart of Accounts. This chart of accounts is very close to the chart of accounts recommended by Habitat International. Both of these chart of accounts are already set up to tie into the IRS Form 990. In fact the biggest change I made to Habitat International chart of accounts was to take advantage of classes to break out the different program costs and the administrative costs. Using classes to separate costs seems to be the trend among non-profits. The QuickBooks reports are geared up to take advantage of classes. I can always go back to the old account numbers. Being on the board of directors I think I would like to see a 990 like report so we can focus on what is program and what is administrative costs.

It took me a couple iterations to create the spreadsheet to transform the Peachtree data into the import format usable by QuickBooks. Splitting the bills into program(i.e. homebuilding costs) and administrative data was the key to increasing efficiency and accuracy. The homebuilding costs are always charged to a house using an item. The administrative costs use expense accounts directly. The import transactions are slightly different but if I used the job ledger report to build the input file for homebuilding costs I had all of the data in one report and I could eliminate manual entries. The purchase report had enough data to build the import file for the administrative costs. The final keys to efficiency was the use of the OFFSET(), ROW(), and VLOOKUP() functions. The OFFSET and ROW functions were necessary because the input and import data was spread over several rows and I wanted to use copy and paste to duplicate the formulas. I found I could minimize my manual entry by using VLOOKUP.