Get Data Only from Certain Records

[gmaxey]

Hi,

I'm a dabbler in VBA and a lower level novice with Excel. However, over the years I have cobbled together some code to get data from an Excel file and poputate a user form listbox. The code is shown below.

The problem is the code populates a listbox with the entire content of the sheet or range. I have a need to fiter out some of the data. For example a column in the sheet might be titled "Exclude" and the values in the records (if that is the right term) migh be "True" or "False." I need to populate my listbox with only the rows that have "True" in that column.

Since I've passed the listbox to the function I know that I can use


.AddItem
and .Listt write record data to specific listbox row and column.

What I don't know how to do is determine which rows to load or not.

Thanks!!

[VBA]Public Function xlFillList(oListOrComboBox As Object, strWorkbook As String, _
strRange As String, bisRangeASheet As Boolean, _
bSuppressHeader As Boolean)
'Fills the passed list or combobox with data from an Excel worksheet or a named range in a worksheet.
' - oListOrComboBox is the name of the list or combo box'
' - strWorkbook is the name of the Excel data file
' - strRange is the part of the data file to be used (sheet or named range).
' - bisRangeASheet sets the range 'strRange' a sheet (true) or a named range (false)
' - bSingleColumn As Boolean configures the list or combobox columns.

If bisRangeASheet Then
'strRange is a named worksheet use:
strRange = strRange & "$]"
Else
'strRange is a named range use:
strRange = strRange & "]"
End If
Set m_oConn = CreateObject("ADODB.Connection")
If bSuppressHeader Then
m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If
m_oConn.Open ConnectionString:=m_strConnection
Set m_oRecordSet = CreateObject("ADODB.Recordset")
'Read the data from the worksheet.
m_oRecordSet.Open "SELECT * FROM [" & strRange, m_oConn, 2, 1
With m_oRecordSet
'Find the last record.
.MoveLast
'Get count.
m_lngNumRecs = .RecordCount
'Return to the start.
.MoveFirst
End With

With oListOrComboBox
'Load the records into the columns of the named list/combo box.
.ColumnCount = m_oRecordSet.Fields.Count
.Column = m_oRecordSet.GetRows(m_lngNumRecs)
End With

'Cleanup
If m_oRecordSet.State = 1 Then m_oRecordSet.Close
Set m_oRecordSet = Nothing
If m_oConn.State = 1 Then m_oConn.Close
Set m_oConn = Nothing
lbl_Exit:
Exit Function
End Function

[/VBA]

[gmaxey]

Figured it out:

[vba]'Load conditionally:
Dim lngIndex As Long, lngCount As Long
Dim lngCols As Long 'A count of fields
Dim lngRow As Long
With oListOrComboBox
.ColumnCount = m_oRecordSet.Fields.Count
lngCols = m_oRecordSet.Fields.Count
For lngIndex = 0 To m_lngNumRecs
'Filter and load only staff in BPT or GPT locations.
Select Case m_oRecordSet.Fields(4)
Case Is = "BPT", "GPT"
.AddItem
For lngCount = 0 To lngCols - 1
.List(lngRow, lngCount) = m_oRecordSet.Fields(lngCount)
Next lngCount
lngRow = lngRow + 1
Case Else
End Select
If lngIndex < m_lngNumRecs - 1 Then
m_oRecordSet.MoveNext
End If
Next lngIndex
End With[/vba]