[RESOLVED] Fill combobox CSV

[JameZz]

Hi,

I want to fill a combox with data out of a colum of a CSV-file. Any suggestions?

[DKenny]

What application are you using?
Does the CSV file have a single column? If not, do you always want data from the same column number?

[JameZz]

I'm using Word -VB6.3. The CSV has more than one column, but it's alway the same. Dependent of this column I would like to fill other textboxes.

[DKenny]

Sorry, I should have asked this question earlier.
Does the column contain unique values? If not, do you need to create a unique set to poulate the combo?

[JameZz]

Yes the column contains unique values.

[DKenny]

OK
Here's an eample how to do this using a textsteam and a FileSystemObject.
You will need to add a reference to the Microsoft Scripting Runtime library in order for this to work.

You will have to change some elements to get this working.
1/ Change the value of sPath to the the correct path for your csv file.
2/ Change combobox1 to the name of your combo.
3/ Change "ThisDocument.ComboBox1.AddItem saCsvData(1)" such that the array element number matches the column you wish to use.
4/ If your csv file has headers, you will not want to add the data from the the first line of the textstream.

Option Explicit

Sub PopCombo()
Dim fsDataFile As Scripting.FileSystemObject
Dim sText As TextStream
Dim sPath As String
Dim sFullLine As String
Dim saCsvData() As String
    
    ' Delete all value currently in the combo
    ThisDocument.ComboBox1.Clear
    
    'Create a new FileSystemObject
    Set fsDataFile = New Scripting.FileSystemObject
    
    'The fully qualified path to your lookup file
    sPath = "C:\book2.csv"
    
    'Create a text stream from your FileSystemObject
    Set sText = fsDataFile.OpenTextFile(sPath, ForReading, False, TristateUseDefault)
    
    'Loop through each line in the text stream
    Do While Not sText.AtEndOfStream
        'Get the complete line of text
        sFullLine = sText.ReadLine
        
        'split the line of text into a 1 dimensional array
        'using comma as the delimeter
        
        saCsvData = Split(sFullLine, ",", 2)
        
        ThisDocument.ComboBox1.AddItem saCsvData(1)
        
    Loop
    
    Set sText = Nothing
    Set fsDataFile = Nothing
End Sub

[JameZz]

Thanks for your reply.

Can I also apply this to a userform? The values are separated by ","

[DKenny]

Yes, you can apply this to a form - if you mean "Can I use this to pouplate a combobox on a form?"

The best way to do this is to convert this to a function that returns an array of values. When you initialize the form you can call this function and then use the resulting array to populate your combobox.
I've change the function so that now you can pass the path to the file, the column number you want to return and the appropriate delimeter for the file. This means that you can use this to populate many different combos (or any other controls) from many different delimited files.
e.g for a TAB delimited file you would pass a value of Chr(9) to the function.

Here's the function

Function GetCSVList(Path As String, Column As Long, Delimiter As String) As Variant
Dim fsDataFile As Scripting.FileSystemObject
Dim sText As TextStream
Dim sFullLine As String
Dim saCsvData() As String
Dim saList() As String
    
    'Create a new FileSystemObject
    Set fsDataFile = New Scripting.FileSystemObject
    
    'Create a text stream from your FileSystemObject
    Set sText = fsDataFile.OpenTextFile(Path, ForReading, False, TristateUseDefault)
    
    ReDim saList(0)
    
    'Loop through each line in the text stream
    Do While Not sText.AtEndOfStream
        'Get the complete line of text
        sFullLine = sText.ReadLine
        
        'split the line of text into a 1 dimensional array
        'using comma as the delimeter
        saCsvData = Split(sFullLine, Delimiter)
        
        'Add the new value and then
        'Increase the size of the return array
        saList(UBound(saList)) = saCsvData(Column - 1)
        ReDim Preserve saList(UBound(saList) + 1) As String
    Loop
    
    'Remove the extra line form the array
    ReDim Preserve saList(UBound(saList) - 1) As String
    
    'Clear Object Variables
    Set sText = Nothing
    Set fsDataFile = Nothing
        
    'Return the functions value
    GetCSVList = saList
End Function

And here's how you would use it to populate a combo.

Private Sub UserForm_Initialize()
Dim vComboList As Variant
Dim lItemNum As Long
 
    vComboList = GetCSVList(Path:="C:\Data\test.csv", Column:=2, Delimiter:=",")
    
    Me.ComboBox1.Clear
    For lItemNum = LBound(vComboList) To UBound(vComboList)
        Me.ComboBox1.AddItem vComboList(lItemNum)
    Next lItemNum
    
    Me.ComboBox1.ListIndex = 0
End Sub

[JameZz]

The displayed value of the combobox is "test". I would like to delete the "" but I can't do by the delimeter..

[DKenny]

We can use the Replace method to remove the double quotes. I would suggest changing the _Initialize event proc to handle this, rather than changing the function.

The best spot to do this is where we are adding the item to the combo.

Me.ComboBox1.AddItem Replace(vComboList(lItemNum), """", "")