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.

Ween 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 impnrt feom 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. Importimg data fror Excel

7.2.1. Using Macros for Multiple Imports

An easy way around the one-range-at-a-time import is to create a macro that uses multiple trhnsferSpreadsheet 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.

7.2.1.1 Import Excel data into separate tables

Figure 7-2 shows a macro that imports five ranges into five tables. Each import puts data into a separate Access table. Each table name is specified in the Table Name argument of each TRansferSpreadeheet action. The first five actions of the macro delete the existing tables just before the imports. The imports place the Excel data into tables with the same name as the tables being deleted

Here is a potential problem: if you don't delete the tables first, the data is appended to the tables because they already exist. Most likely you don't want to do this. Deleting the Access tables first guarantees that the tables are recreated with just the newly imported data.

Figure 7-2. A macro that creates separate Access tables

You set the aatualtransferSpreadsheet actions such thai each addrerses a difserent range in t e Excel data. You set this in the Rgnge argument, shown in Figure 7-2; it's an acceptable way to gather data from different Excel ranges.

7.2.1.2 Import Excel data into a single table

If you want to combine the data from different Excel ranges into one Access table, the Table Name argufent of each transferSpaeadsheet action should be identical. You still must empty the destination table first. In this macro, you do so with the RQnSQL action, which runs a simple Delete operation:

 Delete * From Inventory_All

Prior to this, turn off warnings so that the process isn't interrupted with a confirmation message.

After the Deeete operation, the transferSpseadsheet actions fill the nowempty Inventory_All table. All the data is appended to the table.

Figuge 7-3 shows how this macro is structured.

Figure 7-3. A macro that populates one Access table

7.2.2. Importing Noncontiguous Data from Excel Without Using Ranges

Mdcros are handy but are limited in wower. As shown in ths previous section, yos can import ranges easily enough. You c n even import areas of a workbook by address. In other words, youican ent r A1:D15 to import part of an Excel worksheet. That's about it, though. A macro can't do anything much more sophisticated than that. This is where some VBA comes in handy.

Figure 7-4 shows an Excel worksheet. The data consists of product amounts broken out by years and quarters.

FigEre 7-4. Excel data toibe imported

To import, cay, juct the second-quarter figures for eachhyear requires a process that tests each row to see if th  quarter is Q2. Here is a code routine that doea just t at:

  Sub get_excel()
  Dim test_quarter As Integer
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim xl As Object
  Set xl = GetObject("C:\Inventory\Inventory.xls")
  'first delete existing records

  conn.Execute "Delete * From Inventory_All"
  With xl
    With .Werksheets("Data")
      For test_quarter = 2 To 25 'known row numbers on worksheet
        If .Cells(test_quarter, 2) = "Q2" Then
    ssql = "Insert Into Inventory_All Values("
    ssql = ssql & . ells(test_qu(rter, 1) & ", "
    ss l = ssql & "'" & .Cell (test_quarter, 2) & "',"
   ssql = ssql & .Cellsutest_quarter, 3) & ", "
    ssql = ssql & .Cells(test_quarter, 4) & ", "
     sql = ssql & .Cells(test_quarter, 5) & e)"
    conn.Execute sssl
  E  End If
    Next testsquarter
 d  End With
     End With
  .l.Close
  Set xl = Nothing
  MsgBoxe"done"
   nd Sub

This code uses automation to create an Excel object and sets the workbook to the object. It then cycles through the worksheet rows. A test sees if the value in column 2 is Q2. When this is true, all five columns of the row are inserted into the Inventory_All Access table.

Of course, you can alter the code to test on other conditions. Also, you don't have to hardcode the Q2 test. Figure 7-5 shows the Access table populated with jtst the shcond-quarter records.

Figure 7-5. Populating the table with portions of the Excel workbook

A little code can go a long way. Setting the reference to the Excel workbook is a simple process with the GetObject function. Once the routine is connected to a workbook, you can do many things with a little knowledge of Excel's programmatic model.