VBA, populate Listbox with CSV and use second field as target of another action

Can you explain a bit more about the VBA environment this will run in, since it isn't an MS Office application?

What value(s) do you want in the listbox for the user to view and select from, all columns, or a subset?

~bp

Its a data monitoring application which uses most of the VBA and forms that office can, though it uses an older version.  VB6 vintage I have been told.

I want the listbox (Combobox?) to display only the first field, a "Friendly" name for the user that represents the next 2 fields when they are called. The next 2 fields are actually cryptic filenames that the normal user would have no way of knowing what them meant.

Is there an online reference manual for the VBA capability in the product?  I'm concerned that something created for either standalone VBscript, or for Microsoft VBA, likley will not work without adaptation to your VBA environment.  Things like how to read a file, and how to display a list box could be a lot different.

Do you have MS Office (Excel), if so I can build something basic in that environment that you can look at.  But I'd like to avoid wasting time working on code that won't work for you, make sense?

~bp

Here's a simple Office VBA of reading a text file, splitting the line on commas, and displaying each value.  Inside the loop we could all to the listbox items, but I think that will be different in Office VBA so will need additional info on your environment.

Since we will only be displaying the first column from the trends file, we will need to save it and it's related data in an array, or better still a dictionary object.  Do you know if your environment supports the VBA dictionary object?

Sub Test()

   Dim strTrendFile As String
   Dim strTrendLine As String
   Dim arrField() As String
   
   strTrendFile = "B:\EE\EE28661809\trends.csv"

   Open strTrendFile For Input As #1

   Do Until EOF(1)
      Line Input #1, strTrendLine
      arrField() = Split(strTrendLine, ",")
      MsgBox arrField(0)
   Loop

   Close #1

End Sub

Select allOpen in new window

~bp

It does make sense. If I have the code that works in office and isn't using any esoteric functions exclusive to office, it should work. If is doesn't I may be able to find the difference in syntax.
The IDE's Help|About says Visual Basic 6.3

BTW I didn't see any code or attachments in your post.
The Help file shows Dictionary Object as  a valid

Here's an example of use of the dictionary object:

Sub Test()

   Dim strTrendFile As String
   Dim strTrendLine As String
   Dim arrField() As String
   Dim dicTrends As Dictionary
   Dim strTrend As String
      
   ' Specify trends file location
   strTrendFile = "B:\EE\EE28661809\trends.csv"
   
   ' Create a dictionary for trend data
   Set dicTrends = CreateObject("Scripting.Dictionary")
   
   ' Open trend data file for reading
   Open strTrendFile For Input As #1

   ' Load trend data, add each record to the dictionary
   Do Until EOF(1)
      Line Input #1, strTrendLine
      arrField() = Split(strTrendLine, ",")
      dicTrends.Add arrField(0), arrField(1) & "," & arrField(2)
   Loop

   ' Close trend data file
   Close #1

   ' HERE WE COULD DISPLAY LISTBOX, AND GET A SELECTION
   strTrend = "Trend 2"

   ' Get the file names from dictionary for the desired trend
   MsgBox dicTrends.Item(strTrend)
   
   ' Release dictionary object
   Set dicTrends = Nothing

End Sub

Select allOpen in new window

~bp

I have your example working and it populates the object, but when looking at the watch window I only see the first entry, i.e. "Trend 1" at dicTrends.Item 1.Value. Shouldn't I see the other 2 fields (arrField(1) and (2))  in there somewhere?

Also, I initially said Listbox, perhaps I should have said ComboBox. But in either case, Can I "throw" the entire contents of the first column of the dictionary array into the listbox and combobox while leaving the second and third columns available for use as vars to push into a procedural call? Once the object has been destroyed I cant get to them.

Odd, when I test in Excel I see the items, as below.  You had multiple lines in your input test file, right?  And this was after the read loop finished all records, yes?


~bp

Yes that's what I see but my CSV is:
item1a,item1b,item1c
item2a,item2b,item2c
etc
I was expecting to see 3 items across and x number down.

Ah, okay.  No, in the Watch window you can't see the values associated with each key of the dictionary, only the key values.  But the data is there, and the MSGBOX should demonstrate.  You can repeat that for each item if you want to see the actual data stored in the dictionary.

~bp

How do I address the second and third items? Items b and c ?

I couldn't figure out how to address them. arrField didn't appear to me like it was part of the dictionary object but instead was a holding area until dictionary object could "absorb" them, which I never saw happen. This was reinforced by the fact that I could only see one of the columns in the object which told me that the object did indeed absorb the contents of the file but was hiding the remaining 2 columns from me, or I had missed a step.  

So in my desperation I created 3 separate public global arrays in my application, then using your code as a template for reading and then Splitting the file (never would have gotten to "splitting ") , I created the following code.

Private Sub ComboBoxSetup() 'initializes Arrays
Dim strTrendFileName As String
Dim strTrendFileLine As String
Dim arrField() As String
Dim strTrend As String
Dim i As Integer
Dim x As Integer
Dim y As Integer
'Set File Location
    strTrendFileName = "P:\TrendSelection.csv"
'Open File for reading
    Open strTrendFileName For Input As #1
'Clear Arrays
    Erase FriendlyName
    Erase TrendTGDname
    Erase TrendCSVname
'Add Default Text
    FriendlyName(0) = "Select Trend..."
'Init Loop Counter
    i = 1
'Load all records from the file
    Do Until EOF(1)
        Line Input #1, strTrendFileLine
        arrField() = Split(strTrendFileLine, ",")
        FriendlyName(i) = arrField(0)
        TrendTGDname(i) = arrField(1)
        TrendCSVname(i) = arrField(2)
        i = i + 1
        FriendlyName(i) = "End Of List"
    Loop
'Set ubounds for ComboBoxTrends
    x = i
'Close disk File
    Close #1
'Populate Combo Box
    For i = 1 To x
        ComboBoxTrends.AddItem (FriendlyName(i))
    Next

End Sub

Select allOpen in new window

This works, plus has an unneeded benefit of leaving the arrays content available for anything else to use.
So, what does the dictionary object do that my code doesn't. I mean certainly the dictionary object is a better choice, more than one person has suggested it, but help me understand why.

There are a few benefits to a dictionary that I would bring up.

First, a dictionary is basically an associative array.  What that means is you reference elements by a key value, rather than a positional index like an array.  This can be very helpful when retrieving an element by a key value, or testing if a value exists in the array.

Second, the dictionary manages all the storage, so no need to redim to expand it's size, or adjust element numbers when deleting an element, etc.  Much easier than an array in that regard.

Here's a link to a decent article on dictionaries.

http://windowsitpro.com/scripting/understanding-vbscript-dictionary-object-alternative-arrays

~bp

I think I have gotten enough to make this work, Thanks for the help Bill

Welcome.

~bp