Use SQL with Excel data

[agibsonsw]

Hello again. Excel 2003 or 7.

I have a table of data in Excel and I'm building a userform with two combo boxes on it. I want to show unique items from a column in the first combo box and, when they select an item, to show corresponding unique items in the 2nd combo box.

I'm using the Advanced Filter feature at the moment to draw the unique data I need. However, I think it would be easier to use ADO and use simple SQL statements on the Excel data.

Is there a simple connection string that I can use when drawing data within the same worksheet? Or do I need to use the following example:

Code: Select all

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Andy G\My Documents\Excel Staff.xls;" _
        & "Extended Properties='Excel 8.0;HDR=Yes';"        ' could add HDR=Yes;IMEX=1';"
        
    cnn.Open    ' setting the ConnectionString doesn't open the connection.
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM TestStaff;", cnn, adOpenStatic  'staff is a range name
    ' or FROM [Sheet1$A1:H20];

Thanks, Andy.

[HansV]

You can avoid specifying a literal path+filename by replacing

...;Data Source=C:\Documents and Settings\Andy G\My Documents\Excel Staff.xls;" _

with

...;Data Source=" & ThisWorkbook.FullName & ";" _

The advantage is that it won't matter if you move or rename the workbook.

[agibsonsw]

That's intelligent :)

[agibsonsw]

So I've got this nice piece of code (almost) working:

Code: Select all

Private Sub ComboBox1_Change()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=Yes';"
    cnn.Open
    Set rst = New ADODB.Recordset
    rst.Open "SELECT DISTINCT Surname FROM staff WHERE Office='" & ComboBox1 _
        & "' ORDER BY Surname;", cnn, adOpenStatic
    ComboBox2.Clear
    ComboBox2.Enabled = True
    With rst
         [b].MoveFirst[/b]
         Do Until .EOF
             ComboBox2.AddItem .Fields("Surname")
             .MoveNext
         Loop
         .Close
     End With
     cnn.Close
End Sub

Private Sub UserForm_Initialize()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=Yes';"
    
    cnn.Open    ' setting the ConnectionString doesn't open the connection.
    Set rst = New ADODB.Recordset
    rst.Open "SELECT DISTINCT Office FROM staff ORDER BY Office;", cnn, adOpenStatic  'staff is a range name
    ' or FROM [Sheet1$A1:H20];
    With rst
        .MoveFirst
        Do Until .EOF
            ComboBox1.AddItem .Fields("Office")
            .MoveNext
        Loop
        .Close
    End With
    cnn.Close
End Sub

But if someone deletes the text in the first combo box I receive an error because I can't 'MoveFirst' - there are no records. How should I check if no records are retrieved? I could check .RecordCount but depending on the type of cursor this could return 0 or -1 even if there are records? And I believe .EOF might not work because it might already be at EOF even if there are records? - requiring a .MoveFirst (catch 22)? (I should know this but haven't done it for a while )

I read a warning that Excel sometimes creates a 'ghost' copy of the data-table which can cause problems. So should I not use ADO in this way? Thanks again, Andy.

[HansV]

Testing

If Not rst.EOF Then
...
End If

should work. EOF will only be True if there are no records.

[agibsonsw]

I just found this as well:

Code: Select all

If Not (rst.BOF And rst.EOF) Then

Which, apparently, will only be true if there are no records. Is your version more reliable - it normally is

[HansV]

Just as reliable, not more reliable. But shorter.

[rory]

FYI, there are memory leak issues with using ADO on an open workbook, so it's not usually recommended. If you are going to be running only a couple of small queries once, you are probably OK, but if you run them repeatedly you may have issues.

[Jezza]

Totally agree with Rory, I have recently created an Excel front end to pull off data for a client and noted this mammoth file in the end just got the code to run against a Stored Procedure in the database...upshot was the SQL Server did the donkey work and the Workbook became the result set