Access Hacks

Hack #59. Import Noncontiguous Ranges of Data from Excel

A standard import lets you get only one data range at a time. Here are a couple of workarounds to get you more.

When importing data from an Excel workbook into Access, you can select to import a worksheet or a range. You can select a range only when the workbook includes established named ranges. Figure 7-1 shows the first screen of the Import Spreadsheet Wizard. This wizard appears after you select File → Get External Data and select to import from an Excel file.

Whether you're importing a worksheet or a range, the problem is that you can select only one item in the list. Usually, single worksheets are imported because a wealth of data can sit on a single worksheet. Ranges are a different story. You might need to import more than one range. It's tedious to run the Import Spreadsheet Wizard over and over again.

Figure 7-1. Importing data from Excel

Using Macros for Multiple Imports

An easy way around the one-range-at-a-time import is to create a macro that uses multiple TransferSpreadsheet actions. Each occurrence of this action imports a single range, but you can create a sequence of them in a single macro. You should consider whether the ranges are to be imported as new tables, or whether the ranges are to be accumulated into a single table.

Import Excel data into separate tables

Figure 7-2 shows a macro that imports five ...