load a listbox in a form from data in a spreadsheet using a filtered Excel SQL statement

In my Excel file there is a form and a spreadsheet. The form has a combo box and a listbox: cboFacility is populated by a list of faciltities; and lstConnections is populated by records in the spreadsheet. There are a number of columns in the spreadsheet (col1, col2, col3...coln, colFacility). Records are constantly being added to the spreadsheet via another process.

I want to populate lstConnections with a SQL statement that takes filtered records from the spreadsheet. So when the user selects a facility from the cboFacility, an SQL statement is built like so: "SELECT col1, col2, col3,...coln FROM SPREADSHEET WHERE colFacility = cboFacility.value". Then lstConnections is populated by the filtered records.

if your code already got recor(s) then something like

Please Login or Register  to view this content.

You can do this by connecting to the worksheet as a database. The query can be very complex. Here is a tutorial. It is very fast.

I do this to create reports. However, I don't recommend using Excel as a serious database with critical information. It is very easy to corrupt the workbook. I would recommend using Access or SQL Server Express (free).

I agree, but constraints at the office prevent me from using Access or SQL Server. I have to use Excel.

Thanks. Doesn't answer my question.

I've attached a sample file with the issue i described. When you select a facility from the combo box, a sql query is built using the selected facility as a filter. Then the query is executed, the results sent to a record set. I'm wondering how to populate the listbox with the recordset.

Please Login or Register  to view this content.

Works perfectly. Thanks.