Integrating Excel and Access

Chapter 4. Integration from the Access Interface

Using VBA from Access is very powerful and will be covered fully throughout the rest of the book. However, Access has some very powerful features available right from the user interface for importing, exporting, and linking data with Excel. The ability to do this from the Access user interface makes many tasks easier. For example, suppose you have two Excel spreadsheets of customer data, and you want to know which customers are on one sheet but missing from the other. You could try to use Excel's VLOOKUP functions and possibly even write some code in Excel to search the other worksheet. From Access, though, you can simply link the two worksheets and run the Find Unmatched Query Wizard; this technique will be discussed later in the chapter.

There are many other easy-to-use features covered in this chapter. By the end of this chapter, you will know how to do the following things:

• Import data from Excel into a new or existing table

• Link an Excel worksheet or named range and treat it like a table in Access

• Export an Access table to be analyzed with Excel

• Use the Save As feature to export a table or query

• Save a report in an Excel format

Performing these tasks requires only a few mouse clicks. After using these features, you'll begin to see other possibilities for integrating these applications. Also, you can use Access to perform these tasks on DBF files, fixed-width text files, delimited text files, and ODBC data source tables. Our examples, however, will be focused on Excel.

Importing Excel Data

Excel is commonly used to build a workbook that performs a series of calculations and returns a result set for analysis. Whether you think about it that way or not, that boils down to Excel's core functionality. The convenient thing about leaving data in Excel is that the result set changes as you make changes to the data. However, that is also the inconvenient thing about leaving data in Excel. There will be times when you will want to take a snapshot of result data and not want to have it changed by new data. Consider the input parameters in Figure 4-1 and the resulting data in Figure 4-2.

Figure 4-1. An Excel worksheet providing inputs that drive calculations on the amortization page

Figure 4-2. Result data that changes when you make changes to the inputs on the LoanInformation worksheet

Assume that you want to load that data into a new table in Access, and you don't want it to change. In Access, create a blank database application by going into Access and selecting File → New. Depending on the version of Access, you will either get a brand new empty database or a menu like you see in Figure 4-3. If you see the menu in Figure 4-3, click on Blank Database and it will bring up the dialog box that you see in Figure 4-4. Next, give the database a name.

Figure 4-3. The new database menu that comes up in Microsoft Access 2003 when you select File → New

Figure 4-4. The dialog box that comes up when you want to create a new database, similar to the Save As dialog box that you would see in other Office applications

Tip

Unlike Excel, Access continually saves your progress as you update the data. This is helpful in the sense that you don't work all morning and lose your work due to a power outage or a network drive going down. But it also means that you can't do work and later go back to the point of your last save. The result is that in Excel, you can work in a workbook called Book1 which you don't need to save, while in Access a file must be created and saved.

Once you have done this, you will have a blank database as shown in Figure 4-5.

Figure 4-5. A brand new blank database in Microsoft Access 2003, showing the Tables tab of the database

Now you are ready to import data into this new database application. To accomplish this, right-click on whitespace when you have the Tables tab selected, and you will see the menu in Figure 4-6. On that menu select Import. Access brings up a File Dialog box like the one in Figure 4-7. In the Files of Type box, select Microsoft Excel and then the file that you want. Access brings up the Import Spreadsheet Wizard shown in Figure 4-8. You will notice that you can choose to bring in entire worksheets or named ranges. Using named ranges is very useful if you have data resembling tables throughout one worksheet. However, generally I try to keep data from tables on its own worksheet. In this example, the data needed is in the worksheet called Amortization, so to import it, click it and then Next.

Figure 4-6. A context dialog with Import

Access brings up the screen shown in Figure 4-9. In this case, check the box that says the first row contains column headings and click Next. (If you have headings and do

Figure 4-7. The import file dialog

not check this box, you run the risk of your data types being incorrect.) This brings up the next step in the wizard, shown in Figure 4-10, where you select whether you want to import the data into a new table or an existing table. Since this is a blank database, select In a New Table and click Next. The next step in the wizard, shown in Figure 4-11, allows you to select columns and either change the name or choose not to import it.

Tip

In some cases, you can change the data type, but in many cases you can't. For this example, accept the defaults and click Next. You might have noticed that the Excel worksheet did not have spaces in the column headings; underscore characters were used instead. While this is not necessary, it makes writing queries much easier. If you have spaces in your field names in an Access table, you will need to place brackets —[Table Name]—in your query when you refer to the field in calculations, etc. So if you were forced to import an Excel spreadsheet that did not have good field names as a table, you could edit them in this step.

The next step of the wizard allows you to either pick the field that is the primary key or select the option that there is no primary key. Normally, I let Access add the primary key. However, since you won't have two payments with the same number in this example, you can select Payment as the primary key, as shown in Figure 4-12. The final step in the wizard, shown in Figure 4-13, is to give the table a name. The table will default to the name of the worksheet or named range. I prefer to place the identifier "tbl_" in front of table names, "qry_" in front of query names, "frm_" in front of form names, etc. So my suggestion is to name this table tbl_Amortization.

While this may seem like a lot of steps, the wizard makes it very quick, particularly if you accept the defaults. Also, you will find this to be a welcome change if you ever had to enter the information already in a spreadsheet into a database.

Figure 4-8. The first step of the Import Spreadsheet Wizard, when you can select importing worksheets or named ranges

Figure 4-9. The second step of the importing process, when you can select whether the first row contains the column headings

Figure 4-10. The third step, when you select whether you want the spreadsheet imported into a new table or an existing table

Figure 4-11. The step when you can change field names and choose whether you want to skip importing a particular field

Figure 4-12. The step when you choose which field, if any, to use as the primary key of the table

Figure 4-13. The final step in the wizard, when you choose the name of the table and have the choice of two options for Access to help you and/or analyze your new table

The other benefit to importing data into a new database is that you don't have to go through the grueling process of creating a table using Design View. Access uses the information that you load to determine whether the field should be text, integer, double, currency, etc. This feature can be a real time saver if you have many fields. Sometimes when I am creating a new database, I mock up a few records in Excel and import the spreadsheet just to save time creating and naming tables. Also, if you are working with database novices, they are often more comfortable in Excel. If you need to obtain data from someone who is not familiar with Access, you can give them an Excel worksheet and have them type in the data using the Excel user interface where they feel comfortable. Then you can take the spreadsheet and import the data into your Access database.

Using Excel to Ease Importing

As you saw in Figure 4-10, you can import data into an existing table. Unless the spreadsheet was designed to work with the existing database, however, you will often work with database tables that may not have the same field names (columns) and data types. For example, a cost center in Excel might be a number without leading zeros , while the same column in Access might be text and expect leading zeros.

You can deal with this in Excel. Create a new worksheet and references to the cells that you need on the original worksheet. Once you have done that, create formulas to modify the data so that the field names and data types match those in Access, and save the workbook. Now it is as simple as importing the new worksheet from Access.

An Easy Way to Add Leading Zeros

One of the most common data conversion items is to put leading zeros on a number and treat it like text. Some people simply edit each record by using an apostrophe to signify that they are entering text and then adding zeros where they are needed. Other people attempt to accomplish this by using a format in Excel that appears to add leading zeros. This does not change the underlying data, however. I have also seen people write a series of If...Then statements checking to see how many zeros are needed. While that works, there is a much easier way. Using the RIGHT function, available in Excel and in VBA, you can easily add leading zeros. If you wanted leading zeros for five digits, the formula is =RIGHT(number + 100000,5)—all you need to do is put a 1, the number of zeros needed, and the number after the comma is the number of digits that you want. This works because if you have the number 123 and you need it to be 00123, by adding 100,000 you get the number 100,123. When you take the right 5 digits, you end up with 00123. This accomplishes a task that many people do as several steps in one.

Another easy way to put data from Excel into Access is to use the Copy command in Excel and then, with the table open in Access, use the Edit → Paste Append command from Access. (If you attempt to use the paste command, Access gives you an error message.) You might find that Paste Append still gives you errors. However, Access does create a table of the paste errors for you to review.

You should carefully review the errors to make sure that your tables are not too restrictive. For example, you might find a field in the database listed as required when there are times it is OK for it to be blank. In addition, you also want to determine what is more important, having some of the data for all of the rows or having all of the data for some of the rows. I have been asked to work on many projects where data integrity was an issue, and things like missing customer records, missing billing records, and so forth were problems. Often, there were data issues causing records to not load. While these issues could have also been found by reviewing a log, it is sometimes easier to loosen restrictions so that you can load the data you have and then use exception reports to find out where you need to fill in the data gaps.

You can also end up with unexpected logic errors when importing or pasting data from Excel into Access. In some cases, Access performs the import without noticing the error. This is a particularly large problem when you inadvertently load a number with decimals (double) into an Integer field. When importing data, some common errors to look for are:

Importation of numbers of type double into a field with a field size of integer

Say you are loading a number with decimals, such as prices of raw materials, into a field that was mistakenly created as an integer field. Aside from all of the prices being wrong, it is a difficult item to catch, particularly if you are working with queries that use that data and not at the base table.

Text in an Excel sheet with more characters than the field size allows

This issue is important because you might end up losing data. I have dealt with issues like this on many occasions, particularly with mailing address fields.

Spaces at the end of text

This problem is common when the Excel file was created as an extract from a mainframe or another database. Some database applications will write spaces at the end of each field until it reaches the field size. This will cause problems if you are attempting to join this field with another. Using the TRIM function Trim([FieldName]) in an update query makes quick work of this.

Duplicate primary keys

Sometimes this error lets you know that there are problems with the data, but other times you will find that what you thought was the primary key really wasn't. For example, on many general ledger systems, it might make sense for the Account Number field to be a primary key on a chart of accounts, but you might find that the primary key is really Company Number and Account Number. You can accomplish this in Access, but you would most likely skip the primary key step in the import and set the primary key in Design View.

If you have successfully addressed the issues, this type of integration with Access and Excel can lead to significant savings of data entry time. Keep in mind that if your Excel workbook's primary function is to provide data to a database, it should be designed with ease of importing in mind. If, on the other hand, the database is secondary to the form and functionality of the workbook, I suggest creating an extra worksheet linked to the primary worksheet in the workbook that will be used specifically for importing/exporting data.