Integrating QuickBooks with Other Programs - QuickBooks Power - QuickBooks 2014: The Missing Manual (2014)

QuickBooks 2014: The Missing Manual (2014)

Part IV. QuickBooks Power

Chapter 26. Integrating QuickBooks with Other Programs

Most companies use other programs in addition to QuickBooks to keep their businesses running smoothly. You can use QuickBooks data in other programs to study your company’s financial ratios, calculate employee bonuses based on hours worked and services sold, send special sales letters to customers on their birthdays, and so on. Similarly, other programs may contain data that would be useful to pull into QuickBooks. For example, if you use an estimating program that has all the products and services you sell in its database, there’s no reason to manually enter those in QuickBooks.

QuickBooks doesn’t share its most intimate details with just any program. It reserves its data for a few select programs—or the ones you tell it to play nicely with. For example, you can set up letters in QuickBooks to send to customers, and the program automatically opens Microsoft Word with your customer data merged into form letters and envelopes. If you use Outlook (not Outlook Express) as your contact-management tool, keeping records up to date is easy. By synchronizing your QuickBooks company file and your contact database, you enter changes in one place and the programs automatically copy data from one file to the other.

Programs that can read a QuickBooks company file still have to ask permission to grab QuickBooks data. The QuickBooks administrator (or other QuickBooks users who the administrator anoints) can say whether another program can have access and how much. For software that can’t read a company file directly but can provide valuable assistance processing your financial data (such as Excel, whose spreadsheets can calculate financial ratios that QuickBooks can’t), you can export and import data between programs (Exporting QuickBooks Data).

This chapter describes how to integrate QuickBooks at whatever level of trust you prefer. It also tells you about add-on services that Intuit provides and how to find third-party programs that work with QuickBooks.

Mail Merge to a Word Document

Business communications are the perfect marriage of QuickBooks data and word processing. You can generate letters and envelopes in no time by combining Quick-Books’ customer or vendor contact info and other data with Microsoft Word mail-merge documents. QuickBooks includes dozens of ready-to-mail letters as Word documents that cover the most common business communications, from customer thank-you notes to less-friendly denials of requests for credit. If nothing less than Pulitzer Prize quality will do for your business letters, you can modify the built-in letters and envelopes in Word or write your own.

When you want to prepare letters in QuickBooks, the best way to start is by choosing Company→“Prepare Letters with Envelopes,” which displays a submenu with the following categories (each of which includes several letter templates):

§ Collection Letters include the invoices or statements that are overdue, and remind customers to pay up. QuickBooks automatically pulls the overdue balance and overdue invoices from your company file.

§ Customer Letters pull only the customer’s contact and address information from QuickBooks to address the letter and envelope. The rest of the letter is boilerplate for situations such as thanking customers for their business, apologizing for a mistake, or sending a contract.

§ Vendor Letters include credit requests, disputed charges, payments on your account, and two blank templates for sending mail or a fax to a vendor.

§ Employee Letters cover birthdays, sick time, vacations, and general communications.

§ Letters to Other Names cover a hodgepodge of different recipients, so Quick-Books doesn’t even try to guess what you need. The only template in this category is a blank letter with basic mail merge fields.

§ Customize Letter Templates is a feature that helps you create a brand-new template, convert a Word document into a template, edit an existing template, or organize the templates you already have, as described in the box on Customizing Letter Templates.

NOTE

The first time you choose one of the items on the Company→“Prepare Letters with Envelopes” submenu, QuickBooks may tell you it can’t find the preinstalled letter templates in your company file folder. In that case, it asks if you want to copy its built-in templates. Click Copy to create copies of the built-in templates in the folder with your company file. (If you store letter templates in another folder on your computer, click Browse to choose that folder.)

POWER USERS’ CLINIC: CUSTOMIZING LETTER TEMPLATES

To create, edit, or otherwise manage customized letter templates, choose Company→“Prepare Letters with Envelopes”→Customize Letter Templates. Then, in the “Letters and Envelopes” wizard’s window, choose one of these options:

§ Create a New Letter Template From Scratch. When you choose this option, you can specify the type of letter you want to create and the name of the template. QuickBooks then sends a blank letter template to Word along with the toolbar shown in Figure 26-1. In addition to writing the content of the letter, you can add fields from QuickBooks to automatically fill in your company and customer (or other recipient) info.

§ Convert an Existing Microsoft Word Document to a Letter Template. This option lets you use an existing Word document as the basis for a new template. Once you specify the type of letter, you can then add more text or QuickBooks fields to the document as if you were creating a new template from scratch.

§ View or Edit Existing Letter Templates. You can open and edit any existing template.

§ Organize Existing Letter Templates. When you choose this option, you can navigate through each template category, choose a template, and then delete, duplicate, rename, or move it to a different category.

QuickBooks stores the Word documents for letter templates in a document folder. In Windows 7 and 8, the templates are in C:\Users\Public\Public Documents\Intuit\QuickBooks\Company Files\QuickBooks Letter Templates. There’s a subfolder for each category of template (Collections Letters, Customer Letters, and so on).

In Word, click the Add-Ins tab to see this QuickBooks toolbar.Click one of the toolbar’s drop-down menus, and then choose a QuickBooks field to insert it into the letter for a mail merge.

Figure 26-1. In Word, click the Add-Ins tab to see this QuickBooks toolbar. Click one of the toolbar’s drop-down menus, and then choose a QuickBooks field to insert it into the letter for a mail merge.

Creating Letters and Envelopes in QuickBooks

Preparing any kind of letter with QuickBooks’ letter wizard takes no more than a few clicks, and the collection letter wizard has some extra smarts. For most letters, you can tell QuickBooks whether you want to include active and inactive names and then select recipients. The collection letter wizard can also filter the Customer List by how late payments are.

Here are the steps for creating letters and envelopes using a collection letter as an example:

1. Choose Company“Prepare Letters with Envelopes”Collection Letters.

QuickBooks opens the “Letters and Envelopes” wizard and shows the recipient options for collection letters.

2. Choose options to select who you want to send letters to (Figure 26-2).

QuickBooks remembers the options you choose and selects them automatically the next time you launch this wizard.

NOTE

For non-collection letters, you still choose active or inactive customers (or both) and whether to send letters to each customer or each job, but those are your only choices. For a recall notice, for example, choose the Both option to send the letter to active and inactive customers alike. (The first screen you see for letters to vendors, employees, and people on the Other Names List combines the list of selected names and the options for filtering names.)

For collection letters, on the “Choose the Recipients” screen, QuickBooks initially selects the Both option to include active and inactive customers. The next set of options on the screen lets you choose whether to send a letter to each customer or to the contact person for each job a customer hires you to do. For collection letters, the third set of options asks you to specify how late the payment has to be before you send a letter.

Figure 26-2. For collection letters, on the “Choose the Recipients” screen, QuickBooks initially selects the Both option to include active and inactive customers. The next set of options on the screen lets you choose whether to send a letter to each customer or to the contact person for each job a customer hires you to do. For collection letters, the third set of options asks you to specify how late the payment has to be before you send a letter.

3. Click Next to display the “Review and Edit Recipients” screen.

For collection letters, QuickBooks displays a message if any customers have unapplied credits or payments. Rather than embarrassing yourself by sending a collection letter to a customer whose payments are up to date, you’re better off clicking OK to close the message box and then clicking Cancel to exit the wizard so you can apply credits and payments before preparing collection letters. (The box on Reviewing Customer Credits and Payments explains how to check for customer credits and payments.)

If there aren’t any unapplied credits or payments, QuickBooks automatically selects all the names that match the criteria you specified on the previous screen.

4. If you’ve already talked to some customers and want to remove them from the list, click the checkmarks in front of their names to turn them off.

You can click Mark All or Unmark All to select or clear every name. If you want only a few names, it’s faster to click Unmark All and then click each name you want. The list is initially sorted by name, but for collection letters you can also select the Amount option to sort by the amount that’s overdue if you want to send letters only to customers whose balances are greater than $100, say.

5. When you’ve selected the customers you want to send letters to, click Next.

QuickBooks displays the Choose a Letter Template screen.

6. Select the collection letter template that you want to send, and then click Next.

QuickBooks includes three types of collection letters. The formal one is a straightforward request for payment, the friendly one assumes the customer simply forgot, and the harsh one includes the threat of turning the account over to a collection agency. The friendly and formal letters can’t do any harm, but if you’re considering sending harsh letters, you might want to create your own template for that communication.

To use a different template entirely, select the “Create or Edit a letter template” option. When you click Next, you can choose one of the options described in the box on Customizing Letter Templates (create a new template, convert a Word document, or edit an existing template).

7. In the “Enter a Name and Title” screen’s Name box, type the name you want to include in the letter’s signature block. In the Title box, type the signer’s title.

When you click Next, QuickBooks sends the information to Microsoft Word, as shown in Figure 26-3. It also displays the “Print Letters and Envelopes” screen in the Letters and Envelopes wizard, but you aren’t ready for that screen just yet. Depending on how many letters you’re sending, you might have to wait a few minutes before Word launches with your letters.

All the letters you create are in one Microsoft Word document, with each letter starting on a new page. You can edit the letters in Word to make small changes, but you have to edit each letter individually. (Editing the Word document doesn’t alter the template in QuickBooks.)Word names the file automatically with the type of letter and the date.To print the letters, in Word, choose File→Print.

Figure 26-3. All the letters you create are in one Microsoft Word document, with each letter starting on a new page. You can edit the letters in Word to make small changes, but you have to edit each letter individually. (Editing the Word document doesn’t alter the template in QuickBooks.) Word names the file automatically with the type of letter and the date. To print the letters, in Word, choose File→Print.

NOTE

QuickBooks warns you if there’s any info missing, like the recipient’s address. If there is, you can fill in the missing info in Word or close the Word document, add the missing info to QuickBooks, and then redo the steps in this section.

8. After you print the letters from Word, go back to QuickBooks. On the “Print Letters and Envelopes” screen, click Next if you want to print envelopes that go with the letters you just printed in Word.

If you don’t want to print envelopes, click Cancel. The wizard closes and you’re done.

9. If you clicked Next to print envelopes, QuickBooks opens the Envelope Options dialog box. In the Envelope Size drop-down list, choose the type of envelope you use.

If your envelopes already include your return address, turn off the “Print return address” checkbox.

10.In the Envelope Options dialog box, click OK.

You’ll see a preview of the envelopes in Word and Word’s Envelope Options dialog box.

11.When you’ve got envelopes in your printer, in Word’s Envelope Options dialog box, click OK.

QuickBooks starts printing your envelopes.

12.Back in QuickBooks, in the “Letters and Envelopes” dialog box, click Finish.

UP TO SPEED: REVIEWING CUSTOMER CREDITS AND PAYMENTS

The Open Invoices report shows unapplied credits and payments as well as open invoices, but you can modify this report to show only the payments and unapplied credits that are available. That way, you can apply those payments and credits to your customers’ balances (as explained on pages Seeing What Customers Owe with Reports and Applying Credits to Invoices, respsectively) so that, when you create collection letters, QuickBooks selects only the customers with overdue balances.

Here’s how to produce a report of payments and unapplied credits:

1. Choose Reports→Customers & Receivables→Open Invoices.

2. To see only unapplied credits and payments, in the report window’s toolbar, click Customize Report, and then click the Filters tab. (If the Modify Report dialog box opens automatically, simply click its Filters tab.)

3. In the Filter list, choose Transaction Type (you may have to scroll down to see this option).

4. In the Transaction Type drop-down list, choose Multiple Transaction Types.

5. In the Select Transaction Types dialog box, click Payment and then click Credit Memo.

6. Click OK to close the Select Transaction Types dialog box, and then click OK in the Modify Report dialog box to update the report to show only unapplied credit memos and payments.

For a quick view of one customer’s transactions, open the Customer Center (on the Home page, click Customers) and select the customer on the Customers & Jobs tab. Then, at the top right of the Customer Information panel, click the QuickReport link.

Synchronizing Contacts

If you keep information about contacts in Microsoft Outlook (2003 through 2013)—not Outlook Express—you can synchronize those records with your QuickBooks contact data. In addition to saving time by not having to enter data twice, synchronizing your contact info also helps reduce errors. As long as you enter an update correctly in one program, you’re sure to get the right info in your other contact database. Regardless of which program you update contact info in, you can transfer any changes to the other database.

NOTE

The only time synchronizing doesn’t apply is when you delete contacts. So if you delete contacts in Outlook, QuickBooks doesn’t delete the corresponding records in your company file. (If you really want those records gone, make them inactive, as described on Hiding Records.) On the other hand, if you delete a contact in QuickBooks but don’t delete it in Outlook, it’ll reappear in QuickBooks unless you tell Contact Sync to ignore it (see step 11 on Using QuickBooks Contact Sync for Outlook).

Using QuickBooks Contact Sync for Outlook

If you use Outlook 2003 or later, QuickBooks’ Contact Sync for Outlook tool can help you synchronize contact data. (Contact Sync works only with the 32-bit versions of Outlook 2010 and 2013.) Although the menu item for synchronizing is already on the File→Utilities submenu, you have to install Contact Sync before you get started. Here’s how to download this tool and put it to work:

1. Choose FileUtilitiesSynchronize Contacts.

If Contact Sync isn’t installed, a message box tells you that you have to download and install it. Click OK to do just that. QuickBooks opens a browser window to the QuickBooks Contact Sync for Outlook page (http://support.quickbooks.intuit.com/support/tools/contact_sync). Type the email address you used when you registered QuickBooks (Registering QuickBooks), and then click Continue To Download. You can save the installation file (click Save) or run it immediately (click Run) to install the software.

Installing is easy. First, be sure to close Outlook. Then, run the installation wizard as you do for other programs. Accept the license agreement, choose a destination folder, and then click Next, and the installation begins.

2. After you install Contact Sync, launch Outlook.

Contact Sync features appear on Outlook’s Add-Ins tab.

3. In QuickBooks, log in as the administrator and open the company file you want to synchronize.

4. In Outlook, click the Add-Ins tab, and then click Synchronize Contacts.

A Connecting To QuickBooks message box appears briefly while Outlook and QuickBooks talk to each other. Then, the QuickBooks Contact Sync dialog box opens and selects the company file that’s open in QuickBooks.

NOTE

If you want to import contacts from a different company file, click Cancel and then, in QuickBooks, open the company file with the contacts you want to import.

5. In the QuickBooks Contact Sync dialog box, click Setup to begin the setup in earnest.

The Select An Outlook Folder screen appears. If you’re like most people, you have only one folder for contacts, named something like \\Personal Folders\Contacts or \\Outlook\Contacts, which the wizard selects automatically.

6. If you want to synchronize to a different Outlook folder, choose it, and then click Next.

The Select QuickBooks List Types To Synchronize screen appears.

7. To synchronize all contacts, turn on the Customer, Include Customer Jobs, and Vendor checkboxes, and then click Next.

For each checkbox you turn on, the Setup Assistant creates a subfolder in the Outlook Contacts folder.

8. On the Exclude Contacts From Synchronization screen, turn on the checkboxes for the types of names you don’twant to transfer back and forth, and then click Next.

When you turn on these checkboxes, names assigned to Outlook’s Personal category or marked as Private won’t transfer to QuickBooks.

9. On the “Mapping Customer Fields (Part 1 of 2)” screen, change the mapping of any QuickBooks field that doesn’t point to the right Outlook field, as shown in Figure 26-4. Click Next when the mappings are the way you want them.

The Mapping Customer Fields screen displays the QuickBooks contact fields on the left and its guesses about what Outlook fields they’re equivalent to on the right.

10.If you turned on the checkboxes to import jobs and vendors, repeat step 9 to map job and vendor fields to the corresponding Outlook fields.

Chances are you’ll make the same changes for jobs and vendors as you did for customer fields.

11.On the Set Conflict Action screen, select how you want Contact Sync to resolve discrepancies between Outlook and QuickBooks, and then click Save.

Contact Sync automatically selects the “Let me decide each case” option, which means you get to tell QuickBooks what to do if the contact info in Outlook differs from the data in QuickBooks. If you usually update contacts in Outlook, select the Outlook Data Wins option. If you usually update contacts in QuickBooks, select the QuickBooks Data Wins option instead.

Contact Sync’s Setup wizard makes some astute guesses about which Outlook fields match up with which QuickBooks fields.However, if the selected field isn’t what you want, click the down arrow to the right of the Outlook field’s name, and then choose the correct field.

Figure 26-4. Contact Sync’s Setup wizard makes some astute guesses about which Outlook fields match up with which QuickBooks fields. However, if the selected field isn’t what you want, click the down arrow to the right of the Outlook field’s name, and then choose the correct field.

12.On the “Change Settings or Synchronize Now” screen, if you want to change any settings, click Setup (you’ll retrace your steps until you’re back at the “Change Settings or Synchronize Now” screen). If you’re ready to synchronize, click Sync Now.

The Contact Sync screen keeps you updated on the progress it’s making. When the synchronization is done, the Contact Overview Complete screen appears, showing you how many customers and vendors it found and either matched or added to Outlook.

13.When the Contact Overview Complete screen appears, click Next.

If contacts that already existed in Outlook don’t match up to a QuickBooks list (like Customer or Vendor), the “Select Categories for QuickBooks” screen appears. You can add an Outlook contact to a QuickBooks list by selecting the contact, choosing the appropriate QuickBooks list in the “Select list for contact” drop-down menu, and then clicking Apply.

If the contacts don’t match up to a QuickBooks list because they aren’t business contacts, you can tell Contact Sync to ignore them. Click the first contact and then Shift-click the last contact to select them all. Then, in the “Select list for contact” drop-down menu, choose Ignore, and then click Apply.

After you’ve selected the QuickBooks lists for your Outlook contacts, click Next.

14.On the Accept Changes screen, review the changes that will occur in Outlook and QuickBooks. If you don’t want to make the changes, click Cancel. If the changes are OK, click Accept.

A progress box shows you where Contact Sync is in transferring data. The Synchronization Complete message box appears when your info is synchronized. Click OK and you’re done.

From now on, you can update contact information with just one click. In Outlook, on the Add-Ins tab, click Synchronize Contacts to make Contact Sync analyze the changes in the two programs and update both as necessary.

Working with Other Apps

Because QuickBooks is so popular, Intuit and plenty of other companies offer programs to fill the niches that QuickBooks doesn’t handle—or doesn’t handle the way you want. For example, QuickBooks Pro and Premier’s inventory-tracking feature offers only average cost inventory (Should You Track Inventory with Items?), so many users turn to third-party inventory applications for LIFO (last in/first out) and FIFO (first in/first out) costing. Estimating is another example, and there are a gazillion more. Third-party developers can build products that integrate with QuickBooks by using Intuit’s Software Development Kit (SDK). And Intuit offers quite a few add-ons of its own. This section describes how to find applications that meet your needs and how to set them up to play nicely with QuickBooks.

Finding Add-on Apps

Intuit and other companies offer scads of business services and add-on apps you can purchase for a fee. The price might be worthwhile when you take into account the cost of your employees’ time or—far more valuable—you being able to relax on weekends instead of catching up on company paperwork. You can find many of these add-ons right within QuickBooks. If you don’t find what you’re looking for there, you might have more luck on the Intuit App Center website (http://appcenter.intuit.com) or Intuit Marketplace website (http://marketplace.intuit.com). Here’s some info about places you can look for add-on apps and services:

§ QuickBooks’ windows promote services that relate to the task you’re performing. For example, click Receive Payments on the QuickBooks Home page, and the Receive Payments window’s Main tab displays an icon labeled Add Credit Card Processing, and its Payments tab includes Add Credit Card Processing and Add eCheck Processing icons. You can also access Intuit’s offerings by choosing Help→Add QuickBooks Services.

§ At the bottom of the left icon bar (Menus and the Icon Bars), the section labeled Do More with QuickBooks lists several Intuit services, including payroll, credit card processing, and ordering supplies.

§ The Intuit App Center (http://appcenter.intuit.com) is a one-stop shop for apps that work with QuickBooks: Intuit and third-party apps, web-based apps, mobile apps, and more. You can get to it from within QuickBooks by going to the Home page’s Company panel and clicking the “Web and Mobile Apps” icon, or by choosing Help→“App Center: Find More Business Solutions.”

§ The Intuit Marketplace (http://marketplace.intuit.com) catalogs thousands of third-party desktop and web-based programs that work with QuickBooks data, most of which offer free trials that last from 30 to 90 days. Programs listed in the Marketplace share data with QuickBooks but target different industries or services. Sure, QuickBooks has Premier editions for a few industries (see The QuickBooks Premier Choices); but the Marketplace offers additional software for those industries, as well as customized accounting solutions for industries like agriculture, hotels and restaurants, transportation, and utilities. For example, the Construction/Contractors section has programs that produce estimates and generate documents that conform to industry association standards—while still sharing data with your QuickBooks company file.

The Intuit Marketplace site lists third-party programs in two ways: by industry and by business function. For example, if you’re looking for contact-management software, you don’t have to navigate every industry link looking for programs. Below the Find Apps heading, click Search By Business Need and then, in the list that appears, click Customer Management (CRM).

You can also refine your search for applications that are compatible with your edition of QuickBooks. After you select a category you’re interested in on the main Marketplace page, below the list of industries or business needs, click the down arrow to the right of the Check Product Compatibility box (you may have to scroll down to see it), and then choose the edition you use: Pro, Premier, Enterprise, Canada, and so on.

§ If you use Google to search for basic terms like “QuickBooks third-party application,” you’ll get tons more results. Of course, if you focus your search (by adding “medical office,” for example) you can narrow the results. And, if you use all of the 10 keywords that you can enter in a Google search to describe the Quick-Books add-ons you seek, you might get a few dozen links worth investigating.

§ With websites like Download.com (www.download.com), try using “Quick-Books” as a keyword for a search there. Download.com has dozens of programs that work with QuickBooks, some of which aren’t industry-based at all but are still incredibly valuable. For example, you can download a database driver so you can access data in your QuickBooks company file from your database-management program.

TIP

Before you let a third-party program loose on your QuickBooks company file, you need to check it out. Does it work with your computer’s operating system and your network? Does it have the features you need? Is it easy to use? Does it come with helpful documentation? Back up your company file and any ancillary files (they use the same filename as the company file but have different file types, such as .tlg and .nd). Store the backup on a CD, DVD, or thumb drive, so you can’t overwrite it by mistake. Then, make a copy of your company file specifically for your test. If the third-party program doesn’t pass the test, you can uninstall it and go back to using your regular company file.

Setting Up an Integrated Application

Integrated applications don’t read data from exported text files; they actually access your company file to get info. To protect your data from programs that shouldn’t read your company file, you have to tell QuickBooks which programs you do want digging into your financial data.

Letting programs access your data is something you set up with preferences. In QuickBooks, choose Edit→Preferences→Integrated Applications, and then click the Company Preferences tab, shown in Figure 26-5. There you can turn on the “Don’t allow any applications to access this company file” checkbox to keep all programs out. But if you’re reading this section, you probably want at least one program to access your QuickBooks data.

Only the QuickBooks administrator can give programs access rights.To learn more about how integrated applications work with QuickBooks, click the “Learn about applications that integrate with QuickBooks” link shown here. When you do that, a browser window opens to a support page that describes Intuit Sync Manager, a feature that synchronizes your QuickBooks data with Intuit’s online services and third-party apps that you use.

Figure 26-5. Only the QuickBooks administrator can give programs access rights. To learn more about how integrated applications work with QuickBooks, click the “Learn about applications that integrate with QuickBooks” link shown here. When you do that, a browser window opens to a support page that describes Intuit Sync Manager, a feature that synchronizes your QuickBooks data with Intuit’s online services and third-party apps that you use.

As long as the “Don’t allow any applications to access this company file” checkbox is turned off, when a program tries to access your company file, QuickBooks displays an Application Certificate dialog box. If you’re the QuickBooks administrator or have permission to dole out file access, choose one of the dialog box’s options to set the program’s access to the company file. Obviously, choosing No keeps the program out. But if you want to let the program in, you have three options to choose from:

§ Yes, prompt each time. When you’re letting another program access your data, this is the safest option. The program can get in only when someone with the rights to approve access says so—a small obstacle that prevents someone from breaking in and running the program after hours. If no one who can approve access is available to say yes, the integrated application (or the person who’s running it) is out of luck.

§ Yes, whenever this QuickBooks company file is open. This option is a bit more trusting. As long as someone is working on the company file, the integrated application can access the file without asking permission.

§ Yes, always allow access even if QuickBooks is not running. This is by far the most lenient choice. The integrated program can help itself to your financial data even if no one with a QuickBooks login is working on the file. This option is exactly what you need if the integrated application is a resource hog that you run at night.

When you choose this option, you can specify that the application be a Quick-Books user and that it must log in. Rather than use one of your employee’s logins, create a separate QuickBooks user (Adding New Users) specifically for that integrated application. That way, you can control the type of data the program can access without affecting anyone else’s login, and the Audit Trail report (Checking for Problems) shows the changes the application makes.

TIP

Name the user after the third-party application. For example, call the user Fishbowl if you’re setting it up for the Fishbowl inventory app.

After a program has accessed your company file, you can change its access rights in the Properties dialog box, as shown in Figure 26-6.

Exporting QuickBooks Data

Programs that don’t integrate with QuickBooks can still do things that QuickBooks can’t. For example, you can export a report to Excel and take advantage of that program’s wider range of calculations and formatting options. To get data out of your QuickBooks company file and into another program, you have three choices:

To change an integrated application’s access rights, on the Company Preferences tab, select the application and then click the Properties button. In the Properties dialog box, you can turn checkboxes on and off to remove access or change the level of access the program has. The Details tab shows the program’s name, the company that developed it, its version, and its certificate.

Figure 26-6. To change an integrated application’s access rights, on the Company Preferences tab, select the application and then click the Properties button. In the Properties dialog box, you can turn checkboxes on and off to remove access or change the level of access the program has. The Details tab shows the program’s name, the company that developed it, its version, and its certificate.

§ Export file. You can create a delimited text file (a file that separates each field with a delimiter like a comma or a tab) that contains data from your QuickBooks file. For example, you can generate export files for QuickBooks lists, such as the Item List. For QuickBooks records with contact info, like your Customer:Job and Vendor Lists, you can produce export files that contain all the contact info or only addresses.

§ Report file. You can export any QuickBooks report to a file that you can use in another program. Compared with exporting a list in its entirety, this option gives you more control over how much information you export. If you want to export data from specific customers, specific data from several lists, from transactions, or from specific fields, exporting reports is your only choice. By creating a customized version of the Customer Contact List report or Vendor Contact List report (see Customizing Reports), for example, you can export the same set of records repeatedly, creating delimited files, spreadsheets, and so on. (Chapter 23 covers QuickBooks’ reports in detail.)

§ Excel file. This option is ideal if you’re not sure what info you need and you’d rather delete and rearrange columns in a spreadsheet program. Throughout QuickBooks, you can get to the same Export dialog box that you see when you export reports. For example, at the bottom of the Item List window, click Excel→Export All Items. In the Customer Center’s menu bar, click Excel, and then choose either Export Customer List or Export Transactions. (The Vendor Center and the Employee Center have similar features.) You can then edit the spreadsheet in Excel all you want and transfer the data to yet another program when you’re done. The box on The Easy Way to View Data explains how to use Excel to view your QuickBooks data.

The following sections explain all your options.

Exporting Lists and Addresses

When you export QuickBooks lists as delimited text files, the export file (called an IIF file because of its .iif file extension, which stands for “Intuit interchange format”) contains values from all the fields associated with those lists. Creating export files is easy—the only choice you have to make is which list(s) to export, and then Quick-Books creates an IIF file that contains every field for every list you chose.

Exporting Lists to a Text File

Exporting one or more lists to a delimited text file involves only a few quick steps:

1. Choose FileUtilitiesExportLists to IIF Files.

QuickBooks opens an Export dialog box that contains checkboxes for each list in QuickBooks, as shown in Figure 26-7.

Turning on a checkbox tells QuickBooks that you want to export all the records in that list.If you turn on more than one checkbox, the data for every list you choose ends up in a single export file.

Figure 26-7. Turning on a checkbox tells QuickBooks that you want to export all the records in that list. If you turn on more than one checkbox, the data for every list you choose ends up in a single export file.

2. Turn on the checkbox for every list you want to export into the same file, and then click OK.

If you want to export lists into separate files, repeat these steps for each export file you want to create. For example, if you want to export your Customer List and your Item List to two different files, repeat these steps twice through.

3. In the second Export dialog box (which is basically a Save As dialog box), navigate to the folder where you want to save the export file and, in the “File name” box, type a name for the file.

Your export files are easier to find if you create a folder specifically for them. For example, you could create a subfolder called Export_Files within the folder that holds your company files.

4. Click Save to create the file.

When the QuickBooks Information message box tells you that your data was exported successfully, click OK to close the message box. Now you’re ready to import the file into Microsoft Excel or another program (Exporting Reports).

TIP

Exporting QuickBooks data can help you learn the format you need to import data into your company file, as described on Importing Data from Other Programs. For example, the easiest way to create new budgets is to set them up in an Excel workbook (Creating and Copying Budgets with Excel).

Exporting Addresses

Because QuickBooks has features that generate mail-merge letters, export contact list reports, and synchronize your company file with Outlook, you may not need the tab-delimited address files that QuickBooks produces. However, if you need a text file of names and addresses to import into another program, here’s what you do:

1. Choose FileUtilitiesExportAddresses to Text File.

The “Select Names for Export Addresses” dialog box opens.

2. In the “Select Names to be exported to your Address data file” box, choose the category of names you want to export, and then click OK.

Initially, QuickBooks chooses “All names” to export all the names in your company file. You can also choose categories of names, like “All vendors.” To select individual names, choose “Multiple names,” and then pick the ones you want.

3. In the Save Address Data File dialog box, select a folder where you want to save the exported file, type a filename in the “File name” box, and then click Save.

QuickBooks automatically assigns the file a .txt extension. You can now import it into any program that can handle tab-delimited addresses, such as Excel (see the box on The Easy Way to View Data).

Exporting Reports

As you learned in Chapter 23, you can customize reports so they contain just the information you want, presented just the way you want. When you want to export only some QuickBooks data or export it in a specific way, your best bet is customizing a report and then exporting it.

POWER USERS’ CLINIC: THE EASY WAY TO VIEW DATA

Data is easier to examine when you view an export or import file with a spreadsheet program like Excel. Most programs can open or import delimited text files, but Excel is a master at reading the records stored in these text files and displaying them clearly.

When you export data to a delimited text file and then open it in Excel, the program puts the data into cells in a spreadsheet. Because records and fields appear in neat rows and columns, respectively, you can quickly identify, select, and edit the data you want. Furthermore, you can eliminate entire rows or columns with a few deft clicks or keystrokes.

Here’s how to open a delimited text file with Excel:

1. In Excel, choose File→Open.

2. The delimited text files won’t appear in the Open dialog box at first because they’re not Excel files. Delimited text files come with a range of file extensions (the three characters that follow the last period in the filename), so choose All Files in the file-type drop-down list to make sure you’ll see your delimited file listed.

3. To open the file, navigate to the folder where it’s saved, and then double-click the file’s name. The Text Import Wizard dialog box appears.

4. On the first screen, Excel automatically selects the Delimited option, which is what you want, so click Next. On the second screen, Excel turns on the Tab checkbox, which is also what you want if you’re opening a QuickBooks IIF file, so click Next again.

5. On the third screen, click a column to select it, and then in the “Column data format” section at the top of the dialog box, select the type of data that column represents. Excel initially selects the General option, which works for most data. If a column contains text, select the Text option; for dates, select the Date option, and then choose the date format you want in the drop-down list to the option’s right. To skip the column, select the “Do not import column (skip)” option instead.

6. The “Data preview” table at the bottom of the dialog box shows you what the data will look like after it’s imported into Excel. When its interpretation of the data is correct, click Finish.

Now you can use Excel to rename column headings or to delete the columns or rows you don’t want to import.

If the program you want to import the report data into is fussy about data formats, export the report to Excel, where it’s easier to make changes than in a text-editing tool such as Windows Notepad. You can also export a report to a comma-delimited file if that’s what another program needs.Exporting reports is an effective way to extract some QuickBooks lists like the Item List, Customer List, Vendor List, and Employee List. Here are the different ways you can use this technique:

§ Running a report. Start by running the report you want to export (Running Reports). Then, in the report window, click Excel→Create New Worksheet. The “Send Report to Excel” dialog box that appears lets you choose the file and options for the export, as shown in Figure 26-8.

§ From the Item List window. Open the Item List window (on the Home page, click the Items & Services icon) and then, at the bottom of the list window, click Excel→Export All Items. QuickBooks opens the Export dialog box, which has similar options to the ones you see when you export a report (Figure 26-8).

When you create an Excel workbook by exporting a report, QuickBooks automatically includes a worksheet in it with tips for working with the resulting Excel worksheet.If you don’t need help with Excel, click the Advanced button shown here and then, in the Advanced Excel Options dialog box, turn off the “Include QuickBooks Export Guide worksheet with helpful advice” checkbox.

Figure 26-8. When you create an Excel workbook by exporting a report, QuickBooks automatically includes a worksheet in it with tips for working with the resulting Excel worksheet. If you don’t need help with Excel, click the Advanced button shown here and then, in the Advanced Excel Options dialog box, turn off the “Include QuickBooks Export Guide worksheet with helpful advice” checkbox.

§ From a QuickBooks center. In the Customer Center’s menu bar, click Excel, and then choose Export Customer List or Export Transactions. In the Vendor Center and Employee Center, choose Export Vendor List or Export Employee List, respectively.

Regardless of whether the Export dialog box or the “Send Report to Excel” dialog box opens, you can choose the same basic options for where you want to save the exported report:

§ The Create new worksheet option creates a brand-new Excel worksheet for the report. If you choose Excel→Create New Worksheet in a report window, the “Create new worksheet” and “in new workbook” options are selected, which means QuickBooks will create a new Excel workbook with a worksheet for the exported data, as shown in Figure 26-9. If you export a list and the Export dialog box appears, selecting the “Create new worksheet” option also displays the “in new workbook” and “in existing workbook” options so you can create a new worksheet in an existing file or a brand-new one.

§ The Update an existing worksheet option exports the report to an existing worksheet in an existing file. This option is ideal if you’re planning to calculate ratios and want to update that worksheet to include your most recent financial numbers. When you select this option, the “Select workbook” box appears so you can choose the file and worksheet you want to update. (If you choose Excel→Update Existing Worksheet in a report window, the “Send Report to Excel” dialog box opens with this option selected.)

When you export a report to Excel, the data in the report’s columns and rows transfer into columns and rows in the worksheet. The subtotals in the workbook use Excel’s SUM function to add up the workbook cells that make up the subtotal. If you choose the “Update an existing worksheet” option, QuickBooks can update values in an existing worksheet without overwriting most of the edits you’ve made.

Figure 26-9. When you export a report to Excel, the data in the report’s columns and rows transfer into columns and rows in the worksheet. The subtotals in the workbook use Excel’s SUM function to add up the workbook cells that make up the subtotal. If you choose the “Update an existing worksheet” option, QuickBooks can update values in an existing worksheet without overwriting most of the edits you’ve made.

§ The Replace an existing worksheet option exports the report to an existing worksheet and replaces the contents of that worksheet. In the “Select workbook” box, choose the file and then specify the worksheet you want to replace.

§ The Create a comma separated values (.csv) file option creates a comma-delimited file that you can use with programs that can read files formatted that way.

TIP

The Advanced button in the Export and “Send Report to Excel” dialog boxes opens the Advanced Excel Options dialog box, which lets you set the formatting you want to transfer from QuickBooks to Excel, choose which Excel features you want the workbook to use (like AutoFit to size the columns to display all the data), and select where you want the report’s header information displayed.

After you’ve selected the type of export file and other options, click Export. Quick-Books launches Excel and copies the data in the report to the Excel workbook you specified, placing the data from the report’s columns into the worksheet’s columns, as shown in Figure 26-9.

Customized Exports Using Contact List Reports

Exporting the entire Customer or Vendor List is overkill when all you want are the contacts’ name and email address; that’s where exporting a report shines. By modifying the settings in the Customer Contact List report or the Vendor Contact List report, you can export exactly the fields you want for specific customers or vendors. For example, storing email addresses in QuickBooks is perfect when you email invoices to customers, but you probably also want these addresses in your email program so you can communicate with customers about the work you’re doing for them.

Out of the box, QuickBooks’ Customer Contact List report includes Customer, Bill to, Contact, Main Phone, Fax, and Balance Total columns. The Vendor Contact List report includes Vendor, Account No., Bill from, Contact, Main Phone, Fax, and Balance Total columns. Here’s how you transform these reports into an export tool, using the Customer Contact List as an example:

1. Choose ReportsListCustomer Contact List.

The Customer Contact List report window opens. (For the Vendor Contact List, choose Reports→List→Vendor Contact List instead.)

2. In the report window’s toolbar, click Customize Report.

The Modify Report dialog box that appears lets you adjust the report to filter the data that you’ll export. (See Customizing Reports to learn about other ways of customizing reports.)

3. Click the dialog box’s Display tab (if you’re not already on it) and, in the Columns section, choose the fields you want to export.

The Customer, Contact, Main Phone, and Fax fields might be good ones to export. Then again, they might not. You can add or remove whichever fields you want by clicking a field’s name in the Columns list to toggle that field on or off. If there’s a checkmark in front of the field’s name, the report will include a column for that field.

4. To produce a report for only the customers you want, click the dialog box’s Filters tab. In the Filter list, choose Customer. In the Customer drop-down list that appears, choose “Multiple customers/jobs” to select the customers you want to export.

QuickBooks displays the Select Customer:Job dialog box with the Manual option selected; that’s what you want.

5. In the list of customer names on the right side of the dialog box, click each customer you want to export, and then click OK. Then, in the Modify Report dialog box, click OK.

You see the report with the modifications you’ve made.

TIP

Saving the modified report you just created reduces the number of steps you have to take the next time you export. Memorizing Reports explains how to make QuickBooks memorize a report.

6. In the Customer Contact List report window’s toolbar, click ExcelCreate New Worksheet.

The “Send Report to Excel” dialog box opens. To create a new Excel workbook, keep the “Create new worksheet” option selected and click Export. Your computer launches Excel and displays the report in a workbook.

Importing Data from Other Programs

Importing data from other programs comes in handy mostly for generating lists in QuickBooks. However, you can also import data to generate your chart of accounts (Importing a Chart of Accounts) or to load different versions of your company budget (Importing a Budget into QuickBooks). The biggest requirement for importing data is that the files have to be either Excel workbooks (.xls or .xlsx files) or delimited text files (which separate each piece of information with commas or tabs).

If you’re familiar with importing Excel spreadsheets into other programs, importing them into QuickBooks is a snap. In fact, QuickBooks includes several Excel import templates that walk you through getting your Excel data for customers, vendors, and items into the format that QuickBooks requires. If you’re importing a Customer List, Vendor List, or Item List, don’t even think about importing it as a delimited file. It’s much easier to open that kind of file in Excel and then use the QuickBooks’ Excel import templates. Importing with the Excel Import Wizard Templates inChapter 6 provides the full story on using these templates.

But if you want to import other kinds of lists or a budget, using a delimited file works just fine. The following section shows you how.

Importing a Delimited File

With a delimited file, QuickBooks needs to know the kind of data you’re importing—and it learns that from special keywords for row and column headers. (Keywords are strings of characters in a delimited text file that identify QuickBooks’ records and fields.) Before you import data from another program into a QuickBooks list, you need to know the correct keywords for the fields you’re importing. The easiest way to see the keywords for a list is to export that list from QuickBooks and examine the keywords at the beginning of the rows and at the tops of the columns.

Deciphering keywords requires a smattering of computerese. For example, when you see the column heading “Billing Address,” you know instantly what kind of information you’re looking at. But the only way QuickBooks recognizes the first line of a billing address is from the keyword BADDR1.

Figure 26-10 shows a delimited file in an Excel spreadsheet. (Even though the delimited file is a text file, viewing it in Excel makes the fields easier to read than they would be in a text editor like Notepad, as described in the box on Importing a Delimited File.) Here’s the layout of a delimited file that QuickBooks can read:

§ QuickBooks list keyword. In the first cell in the first row of a list, an exclamation point in front of the keyword tells QuickBooks that the data in the rows that follow are for that list. For example, !CUST (labeled 1 in Figure 26-10) represents data for the Customer List.

§ Field names. Keywords in the other cells of the same row specify QuickBooks field names, like cell B36 in Figure 26-10 (where it’s labeled 2). In that cell, the keyword NAME identifies the values in that column as the name of each customer. In cell G36, BADDR3 identifies the third part of the billing address.

§ Record keyword. A keyword is the first text in the row. Each row that begins with CUST in the first cell (labeled 3 in Figure 26-10) represents a separate customer record.

§ Record values. The other cells in a row contain the values that QuickBooks imports into the designated fields, as shown in the cell labeled 4 in Figure 26-10.

A keyword in the first column specifies which QuickBooks list the data represents. Other keywords in the first column indicate that a row contains a list record.The keywords in row 36 here identify the Quick-Books fields that each column contains.

Figure 26-10. A keyword in the first column specifies which QuickBooks list the data represents. Other keywords in the first column indicate that a row contains a list record. The keywords in row 36 here identify the Quick-Books fields that each column contains.

When you have an IIF file with the correct keywords, here’s how to import it into QuickBooks:

1. Choose FileUtilitiesImportIIF Files.

QuickBooks opens the Import dialog box with the “Files of type” box set to “IIF Files (*.IIF).”

2. Navigate to the folder where the file you want to import is saved, and then double-click the file’s name.

QuickBooks displays a message box telling you that it imported the data successfully. (If you didn’t set up the keywords correctly or QuickBooks ran into other problems with the data in the file, it tells you that it didn’t import the data successfully.)