Populate 2 column listbox using outlook vba

[RajOberoi]

Hi Guys,

I am thinking of a way to populate 2 column listbox based on data stored in a notepad (on the network). I don't want this to be hard coded so if anyone wants to change the options then just make changes i notepad and listbox will get updated automatically.

Can anyone please help me in this?

Thanks

[RajOberoi]

To elaborate I am working on Outlook VBA so I have a userform and there is 2 column listbox on it. I want to store data in a .csv file or any other file that will reside on the network and want to write a piece of code that will store data in a listbox from that particular file on the network.

What is the best way to do it? Any help will be much appreciated.

Thanks

[RajOberoi]

I got the following code but not sure how to make it working for multicolumn listbox . My text file stores data as below:
1,A
2,B
3,C
4,D

I need to make 1st column of the listbox hidden so that users can see A,B,C,D in the listbox .


Private Sub UserForm_Initialize()
Dim fn As String, ff As Integer, txt As String
fn = "C:\Users\drcp\Documents\keywords.txt" '< --- .txt file path
txt = Space(FileLen(fn))
ff = FreeFile
Open fn For Binary As #ff
Get #ff, , txt
Close #ff

Dim myArray() As String
'Use Split function to return a zero based one dimensional array.
myArray = Split(txt, vbCrLf)
'Use .List method to populate listbox.
ListBox1.List = myArray
lbl_Exit:
Exit Sub

End Sub

[gmayor]

Your code does not produce a two column list from the text. You need instead



myArray = Split(txt, vbCrLf)
'Use .List method to populate listbox.
With ListBox1
.ColumnCount = 2
For i = 0 To UBound(myArray)
.AddItem
.List(i, 0) = Split(myArray(i), ",")(0)
.List(i, 1) = Split(myArray(i), ",")(1)
Next i
.ColumnWidths = "0 pt;" & .Width - 4
End With

[RajOberoi]

Thanks for this Graham. How can I display both the columns in the listbox ?

[RajOberoi]

When I put breakpoints and run the code then txt in the highlighted line below gives me 1,A2,B3,C4,D5,E. But my notepad stores data as below:
1,A
2,B
3,C
4,D
5,E



Dim fn As String, ff As Integer, txt As String
fn = "C:\Users\A955724\Desktop\Test.txt" '< --- .txt file path
txt = Space(FileLen(fn))
ff = FreeFile
Open fn For Binary As #ff
Get #ff, , txt
Close #ff


Dim myArray() As String
Dim i As Integer
myArray = Split(txt, vbCrLf)
'Use .List method to populate listbox.
With ListBox1
.ColumnCount = 2
For i = 0 To UBound(myArray)
.AddItem
.List(i, 0) = Split(myArray(i), ",")(0)
.List(i, 1) = Split(myArray(i), ",")(1)
Next i
.ColumnWidths = "0pt;" & .Width - 4
End With

[gmayor]

Remove the line that sets the column widths. The first column is currently set to be 0, so it doesn't display. It is however still present - just not visible.

[RajOberoi]

It worked great, Many Thanks Graham.

[RajOberoi]

Sorry Grayham , there is another thing I want to figure out . If we need to update listbox based on the 'option' selected in a frame so I have written a sub as below. The frame has 3 options 'Pre Completion','Post Completion' and 'Services' . So I have created 3 different text files for those options and trying to store data in a listbox from the selected option.
If it's first option then the code works fine but it gives 'Subscript out of range' for the other 2 options. Can you please figure out what's going wrong. Do we need to clear Array each time ?


Private Sub PopulateListbox()
Dim fn As String, ff As Integer, txt As String


If Me.Option1 = True Then
fn = "C:\Users\A955724\Desktop\Outlook Files\Pre-Completion.txt" '< --- .txt file path
ElseIf Me.Option2 = True Then
fn = "C:\Users\A955724\Desktop\Outlook Files\Post-Completion.txt" '< --- .txt file path
ElseIf Me.Option3 = True Then
fn = "C:\Users\A955724\Desktop\Outlook Files\Mortgage-Services.txt" '< --- .txt file path
End If


txt = Space(FileLen(fn))
ff = FreeFile
Open fn For Binary As #ff
Get #ff, , txt
Close #ff


Dim myArray() As String
Dim i As Integer


myArray = Split(txt, vbCrLf)
'Use .List method to populate listbox.
With ListBox1
.ColumnCount = 2

For i = 0 To UBound(myArray)
.AddItem
.List(i, 0) = Split(myArray(i), ",")(0)
.List(i, 1) = Split(myArray(i), ",")(1)
Next i
.ColumnWidths = "0pt;" & .Width - 4
End With
End Sub

[RajOberoi]

Now I deleted few items in my 1st text file and it's giving 'Subscript out of range' for that as well.

[gmayor]

Assuming that each of the text files has two items per line, each separated by a comma (and only one comma in the line) then the issue is undoubtedly that you pressed enter after the last item in the text files and thus created an empty paragraph without a comma. You can error trap that ... and you should clear the list box before creating a new list.


On Error Resume Next
With ListBox1
.ColumnCount = 2
.Clear
For i = 0 To UBound(myArray)
.AddItem
.List(i, 0) = Split(myArray(i), ",")(0)
.List(i, 1) = Split(myArray(i), ",")(1)
Next i

[RajOberoi]

Many Thanks Graham. It worked great. :)