gmaxey
05-21-2013, 02:01 PM
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!!
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
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!!
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