DataWright Information Services

Consulting and Resources for Excel and Access

Use a validation list to import data from an Access query

This is Part 5 of 7 in a tutorial on using code to integrate
Excel and Access.

In the sample file, Region has a validation list
in cell K1. Selecting a value from the list triggers some event
code, which in turn drives the code to download data for all
countries from that region.


  1. Because all of the code for this tutorial exists in a single
    module, I have placed a
    constant at the top of the module that
    defines the name of the database. This removes the need to keep
    redefining it for each procedure, and means that you only need
    to change one place in the code to refer to a different
  2. To use this code in your own file you will need to set the
    appropriate references. In the code environment go to Tools >
    References and select Microsoft ActiveX Data Objects 2.x
    (the highest version available on your system).

The constant is shown below. It needs to go before the first Sub
procedure in the module:

Const TARGET_DB = "DB_test1.mdb"

To view the event code, right-click the worksheet tab and
View Code
. The code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target = Range("K1") Then Call DownloadRegion
   Application.EnableEvents = True
End Sub

Let’s dissect the code.

Private Sub Worksheet_Change(ByVal Target As Range)

When you make a change on a worksheet, Excel can respond to this
by triggering other actions. It does that by using code in the
Worksheet_Change procedure, and referencing the cell(s) that
changed. The changing cells are referred to as the Target.

   If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target = Range("K1") Then Call DownloadRegion
   Application.EnableEvents = True

When you use event code on a worksheet you stand a chance of
slowing your application to a crawl, because every change on the
worksheet will trigger the event and you may find yourself stuck in
a loop. This code does three things to avoid the problem.

  1. First, if more than one cell is selected when the change
    happens, the first line causes the code to terminate.
  2. Second, we only want the code to run if K1 is changed so the
    third line attaches the code to that cell only.
  3. Third, because the code will change multiple rows of data,
    we don’t want the code firing for every cell that is updated. We
    wrap the call to DownloadRegion in two lines of code; the first
    temporarily turns off events in the application, and the second
    switches them back on once DownloadRegion has finished running.

Note: The code will still run properly without those two lines.
It will just be slower, because of all the additional
Worksheet_Change events that are triggered. You can test this out by
commenting out the two Application.EnableEvents lines and comparing
the difference.

The second part of this example is the code that downloads the
data. It goes in a standard module but is called from the
Worksheet_Change event. The code for DownloadRegion is shown below:

Sub DownloadRegion()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim i As Long
   Dim ShDest As Worksheet
   Dim sSQL As String

   Set ShDest = Sheets("Region")

   sSQL = "SELECT * FROM tblPopulation WHERE Region ='" & ShDest.Range("K1").Value & "'"

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockOptimistic, _

   'clear existing data on the sheet
   Range("A1").CurrentRegion.Offset(1, 0).ClearContents

   'create field headers
   i = 0
   With Range("A1")
   For Each fld In rst.Fields
     .Offset(0, i).Value = fld.Name
     i = i+ 1
   Next fld
   End With

   'transfer data to Excel
   Range("A2").CopyFromRecordset rst

   ' Close the connection
   Set rst = Nothing
   Set cnn = Nothing
End Sub

If you compare this code to the sample in
part 4 of this tutorial,
you will see that they are very similar. The only difference is in
how the recordset is defined. Click
here to
see a summary of some crucial recordset differences.

Part 4

Next: Part 6>>