I get asked how to hold an Excel query table in memory from time-to-time, so it’s time for a post about it. You can use ADO (ActiveX Data Objects) to query a database object and get a recordset without having to write it to a worksheet. This post shows how I do it, and I’m no expert on the subject, so be sure to read the comments for errata and performance issues.
First, I create an Excel external data table normally through the Data > External Data menu. Then I copy the pertinent information for that query to my code. Finally, I delete the external data table. I do this because I don’t want to remember how to write connection strings and sql statements. I know, I’m lazy.
With the external data table created, I go to the Immediate Window to get what I need.
The CommandText property is the SQL statement (it was the SQL property in Excel97 and that still works in later versions). The Connection property is the connection string you’ll need to get access to the database. I paste these strings into variables in my code, clean the up a little, and add line continuation characters for easier reading.
Next, I set a reference (VBE – Tools > References) to the Microsoft ActiveX Data Objects x.x Library. Generally, pick the highest number for x.x that shows. Mine starts with 2.0 and goes to 2.8, but not every number in between is there.
Here’s the commented code to create and read the recordset. All it does is create a list of customer Ids for every customer that’s in London.
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String
'Copied from the immediate window "Connection" and removed the
'ODBC from the beginning
sConn = "DSN=MS Access Database;" & _
"DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;" & _
"DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;" & _
'Copied from the immediate window "CommandText" and cleaned it up a little.
sSql = "SELECT CustomerID, CompanyName, Address, City, Region, PostalCode" & _
" FROM Customers" & _
" WHERE (City=’London’)"
'Create a new connection and open it
Set adoConn = New ADODB.Connection
'Create a new recordset and open it. There are probably some
'advantages to using different cursor types, but I generally use
'the default (adOpenForwardOnly) unless I know I need to go backward
'then I use adOpenDynamic
Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
If Not (adoRs.BOF Or adoRs.EOF) Then 'If there are no records, this will be false
adoRs.MoveFirst 'This probably isn’t necessary, particularly with the default
'cursor type,but I always include it anyway
Do While Not adoRs.EOF 'Start looping through the records
sOutput = sOutput & adoRs.Fields(0).Value & "," 'Concatenate a string
'of the first field
adoRs.MoveNext 'When you code has been running for 1/2 an hour, you
'probably forgot this line. I know I forget it all the time.
'Get rid of the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)
sOutput = "Empty Recordset"
Set adoRs = Nothing
Set adoConn = Nothing
The result, if you’re playing at home