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

This code is like Office VBA but is not using Office itself.

I will have a CSV file called Trends.csv

it will contain the following 3 field text by x number of rows:
Trend 1,File1.tgd,File1.csv
Trend 2,File2.tgd,File2.csv
Trend 3,File3.tgd,File3.csv      etc...

Open in new window


I want to populate a listbox using this CSV file, then when I click on an item in the listbox (File 2 for example) I want to call a subroutine that uses the second field of that row (File2.tgd). in the following line,
loadfile "File2.tgd"

Open in new window

And the third field of that row in the following line:
applyFile "File2.csv"

Open in new window


I can't find a starting point since I cant figure out how to split the fields into seperate string vars to use as I have shown.

I would appreciate some help, this is probably simple for someone else.
LVL 1
Salad-DodgerInstrumentationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
0
Salad-DodgerInstrumentationAuthor Commented:
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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Bill PrewIT / Software Engineering ConsultantCommented:
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

Open in new window


~bp
0
Salad-DodgerInstrumentationAuthor Commented:
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
0
Salad-DodgerInstrumentationAuthor Commented:
BTW I didn't see any code or attachments in your post.
The Help file shows Dictionary Object as  a valid
0
Bill PrewIT / Software Engineering ConsultantCommented:
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

Open in new window

~bp
0
Salad-DodgerInstrumentationAuthor Commented:
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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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?

Watch Window
~bp
0
Salad-DodgerInstrumentationAuthor Commented:
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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
Salad-DodgerInstrumentationAuthor Commented:
How do I address the second and third items? Items b and c ?
0
Bill PrewIT / Software Engineering ConsultantCommented:
They are stored in the data payload for the trend keys, so you can just get it, split on the delimiter (I used a comma), and then access the fields that we stored there.

Here's an example of that:

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)
   arrField() = Split(dicTrends.Item(strTrend), ",")
   MsgBox "First data dile = " & arrField(0)
   MsgBox "Second data dile = " & arrField(1)
   
   ' Release dictionary object
   Set dicTrends = Nothing

End Sub

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Salad-DodgerInstrumentationAuthor Commented:
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

Open 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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
Salad-DodgerInstrumentationAuthor Commented:
I think I have gotten enough to make this work, Thanks for the help Bill
0
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.