Link to home
Start Free TrialLog in
Avatar of BryanKipp
BryanKipp

asked on

Filter Data Passed into a Combobox based on Column Data in a CSV File

I have the following code populating a combobox from a .CSV file and would ike to know if there is a way to add filtering based on an added column.

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window

The .CSV file is as follows:
Annapolis Hill Discharge Pressure;[CR]AH_DISCHARGE_PRESSURE;PSI;0;500;[Test];Building 1
Annapolis Hill Suction Pressure;[CR]AH_SUCTION_PRESSURE;PSI;0;500;[Test];Building 1
Annapolis Hill Tank Level;[CR]AH_TANK_LEVEL;FT;0;40;[Test];Building 1
Backwash Flow Control Valve Position;[CR]BKWASH_FCV_POS_IN;Percent;0;100;[Test];Building 2
Backwash Flow;[CR]BKWASH_FLOW;GPM;0;250;[Test];Building 2
Backwash Flow Set Point;[CR]BKWASH_FLOW_SP;GPM;0;250;[Test];Building 2
Chaffee Hill Discharge Pressure;[CR]CH_DISCHARGE_PRESSURE;PSI;0;500;[Test];Building 3
Chaffee Hill Suction Pressure;[CR]CH_SUCTION_PRESSURE;PSI;0;500;[Test];Building 3
Chaffee Hill Tank Level;[CR]CH_TANK_LEVEL;FT;-25;-5;[Test];Building 3
Clear Well Level 1;[CR]CLEARWELL1_LEVEL;FT;0;10;[Test];Building 4
Clear Well Level 2;[CR]CLEARWELL2_LEVEL;FT;0;10;[Test];Building 4
Clear Well Level 3;[CR]CLEARWELL3_LEVEL;FT;0;10;[Test];Building 4

Open in new window

I would like to be able to allow the user to filter the data that populates the comboboxes by selecting the area.....example Building 1, and only have the building 1 data populate the box. If no filter is applied, they would see all data from all areas.


======
Related Prior Question : https://www.experts-exchange.com/questions/27026600/Load-Column-Data-into-a-ComboBox-from-a-csv-file.html
Avatar of judgeking
judgeking
Flag of Canada image

Is this a VB6 app?
Avatar of BryanKipp
BryanKipp

ASKER

It is VBA 6.5
It this an impossibility? Or is there some better way to accomplish what the original question addressed that incorporates this into the final solution?
Avatar of Jeffrey Coachman
<It this an impossibility?>
Very little is truly "Impossible"
;-)

The unorthodox way you are loading the combobox (via a csv disk file), make this "Difficult".

This would be *much* easier if you simply imported this file (possibly as a temp table) int the DB...


JeffCoachman
I'm not sure what is going to be the criteria that will dictate that a building subset or all buildings should be displayed from your example, but try something like this modification:

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
[b] Dim bolInclude as boolean        'Boolean to trigger inclusion or not of any given building choice
 Dim strControlString as string   'Your filter string from somewhere else that is either blank 
                                         '(to include ALL), or equal to the building string you want to include
                                         '(i.e. "Building 1", etc.)[/b]

 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear

'[b]strControlString = ""            'This will include all buildings.
strControlString = "Building 1"   'This will only include the rows that have matching building strings.[/b]


While Not EOF(1)
[b]   strContent = Split(strHeadings, ";", 2)

   bolInclude = True               'Assume to include all buildings
   'Then check to see if there is any control string, and if there is, assign bolInclude
   'to be True or False if your 6th element in the CSV string matches the required Building string.
   IF Len(strControlString)<>0 THEN bolInclude = (strContent(6)=strControlString)

   'Now only add the element if we've determined that the string should be included in the combo.
   IF bolInclude THEN[/b]
       Pen1_ComboBox.AddItem strContent(0)
       Pen2_ComboBox.AddItem strContent(0)
       Pen3_ComboBox.AddItem strContent(0)
       Pen4_ComboBox.AddItem strContent(0)
       dicTags.Add strContent(0), strContent(1)
[b]   END IF[/b]
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window


There are many ways to achieve the same results, but this is pretty readable.
I see the Bold tags didn't work inside the code section, so when you see those, that is the modified code within your code example.  Hope that helps.
...except for the "strContent = Split(strHeadings, ";", 2)" line.  (Doh!)
...Then it would just be a matter of filtering a form based on a unique list of Buildings form the list.

Sample attached


Can you explain why this has to be done via a disk file?
And explain the ultimate function of this system?

In any event, your ultimate goal here should be to create a "Building" Table and load the combobx with that.
Then filter the form based on the value in the combobox.

JeffCoachman

db350.mdb
BryanKipp, are you trying to filter which records show in a form or what options are on a combobox list?  boag2000 is making me think I misunderstood the question when he/she mentions filtering a form, but I understood from your original question when you stated "add filtering based on an added column" that you wanted to have a combobox automatically adjust what options/choices are available on that combobox list based on another field on the form (not identified here in the question).  
Can you clarify?
To attempt to answer the questions...

The reason for using  the .csv file is that, in the system I'm working with, generating this file is fairly simple and easy to modify. We won't have any type of DB software, nor Excel to use any other type of file. I'm not certain I understand the comment above about importing this file vice loading it at run time. Once this system is operational, this file will likely not be altered much, but if it was, I would still need to be able to "load" the aterations.

The purpose of the system is to allow a user the ability to select the values (Pens) they wish to do historical trending on in an automation system. The need to see different specific values is based on what events might be occurring in their system.

The idea of adding the ability to filter the list is based on trying to "clean up" the list for the user so they don't have to wade through every possible item to find the "tag" they wish to trend.

I don't know if this helps, or if you need to see the actual complete code I am currently using for any further clarification. I will try the code listed above to see if that gets me where I need to be.

Thanks for your assistance!
Also, for further clarification, this is a VBA 6.5 application. Not sure why the question got moved into the Web Language zone.
<We won't have any type of DB software,>
<VBA 6.5 application>
VBA really can't work on it's own.
It needs an "Application" (MS Office Application) in order to function...

So then can you state the specific Office App you want this solution to run in?
If you are not using Access or Excel...is this an Word combobox?

Did you test the sample File I posted?
What is it lacking?
It appears to do all that you are requesting...

JeffCoachman
This code is running in a Rockwell Software application, but their VBA behaves the same as an Office application.

I did test the code you sent, it generates an error : "Subscript out of range" on line 34 of the code
<I did test the code you sent, it generates an error : "Subscript out of range" on line 34 of the code>
...?
In the file I posted there is only about 6 lines of code?

If you mean you simply took my code and pasted into your app, then I can't tell you what might happen.

Perhaps another Expert can help you further...
The code I tested was what was provided by kbirecki. It generated the error. The .MDB file attached to your post is something that I would try if we had any sort of dataase software on the computers.

Perhaps it would be best if I withdraw this question and repost the entire question to get a complete solution. Thus far, it has been accomplished in two steps with this being the final (third) step. There may be a better way of accomplishing the desired result if I go about the solution a diffrent way.
@kbirecki

>>...except for the "strContent = Split(strHeadings, ";", 2)" line.
re: http:#35743127 comment
That statement came from the prior question and was a way to store the tag data in a dictionary object, with the first parsed part being the key and the remaining (unparsed) parts being the data.  Later in the application, the user would select a combobox item and the dictionary's hashed lookup provided a string that could be Split() to provide parameter data to a function.
@BryanKipp

>>Perhaps it would be best if I withdraw this question...
No.  It would be best if you stuck with the experts in this thread.
My comment wasn't intended as a slight to anyone in this thread. My apologies if it came across that way. It just appears this will be difficult to solve, and wasn't sure if the entire approach would be better some other way.

A brief synopsis of the issue.
VBA 6.5
No Office or DB software available.

1: I need the ability to populate a combobox with data from a single column in a .csv file. Currently loading that file when a display is opened. If it would be better to have that file imported rahter than loaded, it wouldn't be an issue.

2. Need to parse data from a row in the .csv file to pass to an argument that adds trend pens to a historical trending screen.

3. Need the ability to filter the data that populates the original combobox so that a user can choose to look through all data, or only certain area data. This could be done either with another combobox, or with buttons.

Numbers 1 and 2 are functioning very well. The combobox is being currently populated when the screen is opened. This is also something that could be changed to require a button push after the filter is applied. Currently, the filter is the 6th column of data in the .csv. This could be moved into its own file, or the column could be moved within the .csv if that makes it easire to accomplish the desired result.

Thank you all for being patient with a relative newbie. Your expertise is incredible!
@BryanKipp

This should look something like this:
Dim strFilter As String
Dim vItem As Variant
strFilter = txtFilter.Text

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem)  Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  End If

Next

Open in new window

Please forgive what is likely an obvious question...

Were would in insert this. I wrapped the IF/ENDIF around my ComboBox.AddITem strContent(0) and it appeared to get stuck in an endless loop

Code is below:
strFilter = txtFilter.Text

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   Pen5_ComboBox.AddItem strContent(0)
   Pen6_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
End If
Next
Wend

Open in new window

The loading of the dicTags must come before the filtering.  It is most likely that you would load the dicTags data when the program initializes or first form loads.  It is only when the user wants to filter the data that you would clear a listbox/combobox and then use the code snippet I posted to fill it with items that match the filter, via dicTags iteration.
I'm getting a "Type Mismatch" in line 5 of your code snippet.

I did find a cumbersome way around all this. I'm manually inputting the Filter areas into a combobox, then have a piece of code that looks for the existence of a filter and loads different .csv files based on the filter criteria.

It has become painfully obvious to me that I need to do more learning of how some of these functions work! I thought I had a grasp on the dictionary object, but have figured out that my understanding is flawed.

I don't want to waste any more of your time on this, but if someone has the time and could post a working piece of code that accomplishes the entire goal, it would be great!! I will go ahead and post the working code and the .CSV file for reference. ArrayTags.CSV
Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
  
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear
Pen5_ComboBox.Clear
Pen6_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   Pen2_ComboBox.AddItem strContent(0)
   Pen3_ComboBox.AddItem strContent(0)
   Pen4_ComboBox.AddItem strContent(0)
   Pen5_ComboBox.AddItem strContent(0)
   Pen6_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Function Add_Trend_Pens() As Boolean
Dim strParsed() As String


On Error GoTo ErrorHandler

If Pen1_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen1_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen1_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen2_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen2_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen2_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen3_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen3_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen3_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen4_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen4_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen4_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen5_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen5_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen5_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If

If Pen6_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen6_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(0) & "}", Pen6_ComboBox.Value, strParsed(1), CSng(strParsed(2)), CSng(strParsed(3)), strParsed(4)

End If


Add_Trend_Pens = True

Exit Function

ErrorHandler:

    Add_Trend_Pens = False
    MsgBox "Error adding trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function
Function Remove_Trend_Pens() As Boolean

On Error GoTo ErrorHandler

While Example_Trend.Pens.Count > 0
    Example_Trend.Pens.Remove (1)
Wend

Remove_Trend_Pens = True

Exit Function

ErrorHandler:

    Remove_Trend_Pens = False
    MsgBox "Error removing trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function

Private Sub RefreshTrend_Button_Released()

On Error GoTo ErrorHandler

' Uncomment out the if statement below to remove the pens

If Remove_Trend_Pens() = False Then
    Exit Sub
End If

' Uncomment out the if statement below to add the selected pens

If Add_Trend_Pens() = False Then
    Exit Sub
End If

'Add the Trend command to make the trend refresh the chart with the new pens
'Here is a hint.  Type example_trend. below and then select the method from this list

Example_Trend.RefreshChart

Exit Sub

ErrorHandler:
    
    MsgBox "Error refreshing trend.  Error:  " & Error, vbOKOnly, "Trend Error"

End Sub
Private Sub RemovePens_Button_Released()

Do
    Example_Trend.Pens.Remove (1)
Loop Until Example_Trend.Pens.Count = 0

End Sub

Open in new window

@BryanKipp

What kind of user interface do you present for the user to specify a selection criterion?

What do the six Pen combobox controls represent?
This application buids an operator interface in an automation platform for a water treatment plant. This particular screen creates a historical trend of actual floating point data coming from the various processors in the plant. The software package is Rockwell Software FactoryTalk ViewSE. It comes with VBA 6.5 as an included package.

What I am allowing the user to do is to select which values they want to see trended. They may want to see different data based on the current plant status. The interface has the actual trend, the buttons to refresh the trend and remove pens, and the six comboboxes.
what about the relationship between these controls and the filtering?
There are several major areas within the total system. There is a possibility within the system that we could have as many as 200 tags we could select from to trend. The filtering is designed to be able to break the number of tags down to a more manageable number to search through when setting up the trend.
>>I'm getting a "Type Mismatch" in line 5 of your code snippet.

which code snippet? (which comment?)
It was in Answer ID: http:#35754357

I'm of the belief there is likely a better way to accomplish this if I start from the beginning. Rahter than adding the filtering at the end, it should probably be incorporated at the beginning with the filters applied before I ever load the comboboxes. I will need a better understanding of the dictionary object and will research that this evening.
do you still have dicTags defined?
Did you place this code snippet in a different form?
I started with the code that was currently working, so yes the dicTags are still defined.

If you look at answer ID: http:#35754575 , you will see where I modified that code.
>>If you look at answer ID: http:#35754575 ...
1. If this is the place where you are filtering the comboboxes, it is assumed that  dicTags has already been populated.  Therefore, you shouldn't have and file I/O or population of dicTags statements.

strFilter = txtFilter.Text
Pen1_ComboBox.Clear
Pen2_ComboBox.Clear
Pen3_ComboBox.Clear
Pen4_ComboBox.Clear
Pen5_ComboBox.Clear
Pen6_ComboBox.Clear

For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
  
    strContent = Split(strHeadings, ";", 2)
    Pen1_ComboBox.AddItem strContent(0)
    Pen2_ComboBox.AddItem strContent(0)
    Pen3_ComboBox.AddItem strContent(0)
    Pen4_ComboBox.AddItem strContent(0)
    Pen5_ComboBox.AddItem strContent(0)
    Pen6_ComboBox.AddItem strContent(0)
  End If
Next

Open in new window


2. The comboboxes would be populated after dicTags has been populated.  However, there would not be any filtering done, since we're probably in the Form_Load stage of UI creation.  All the items would be added to all six combobox controls.
What I have posted below seems to try to work, but it only populates the box with the last line of the .CSV file.
Option Explicit
Dim dicTags As Object
Dim strHeadings, strContent() As String
Dim iCtr As Integer
Dim vItem As Variant



Private Sub Display_AnimationStart()
Set dicTags = CreateObject("Scripting.Dictionary")

 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings



Pen1_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

End Sub

Private Sub btnLoad_Click()
Dim strFilter As String

strFilter = txtFilter.Text
For Each vItem In dicTags
  If (vItem Like "*" & strFilter & "*") Or (dicTags(vItem) Like "*" & strFilter & "*") Then
    'add vItem to a chosen/filtered list
    Pen1_ComboBox.AddItem strContent(0)
  End If
Next

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much for your patience. This is now fully finctioning and will make the boss and the customer very happy!