Normally, Data > Get External Data is a fine option for using data from an external source in you worksheets. There are situations, though, where the structure of an external data table isn’t what you want. You may want to use pieces of the data in various places, rather than in a tidy table.
In a situation I recently encountered, I needed the user to specify a key field of a recordset and have certain information returned. Normally, I would create a hidden worksheet and put an external data table on it with the whole recordset. Then, I would use VLOOKUPs to get at the data based on the user’s entry. The problem was that the recordset was more than 65,000 rows. I tried to think of a way to limit the data in the recordset, but I couldn’t predict what the users would enter, and therefore needed access to the whole thing.
I ended up using a user-defined function with ADO automation. Here’s generally what it looked like. This example uses the Products table of Northwind.mdb.
Dim adoRs As ADODB.Recordset
Function GetFields(sKey As String, lField As Long) As Variant
Dim sCon As String, sSql As String
‘Create recordset if first call
If adoCn Is Nothing Or adoRs Is Nothing Then
sCon = “DSN=MS Access Database;” & _
“DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;” & _
“DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;” & _
sSql = “SELECT ProductID, ProductName, QuantityPerUnit, Products.UnitPrice “ & _
Set adoCn = New ADODB.Connection
Set adoRs = New ADODB.Recordset
adoRs.CursorType = adOpenDynamic
adoRs.CursorLocation = adUseClient
adoRs.Open sSql, adoCn
adoRs.Find “ProductID=” & sKey
If adoRs.EOF Or adoRs.BOF Then
GetFields = “Not found”
GetFields = adoRs.Fields(lField).Value
And it was used, more or less, like this: