Store data in multicolumn listbox from a text file (1 Viewer)

aman

Registered User.
Local time
Today, 09:38
Joined
Oct 16, 2008
Messages
1,250
Hi guys,

My code is written in outlook vba and I am trying to figure out the issue with storing different data each time in the listbox based on the relevant option selected on the form.

So, If we need to update listbox based on the 'option' selected in a frame , 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 stored them on the network.

It gives 'Subscript out of range' error message in the below highlighted line of code. Can you please figure out what's going wrong. Do we need to clear Array each time ?
Code:
Private Sub PopulateListbox()
Dim fn As String, ff As Integer, txt As String


If Me.Option1 = True Then
   fn = "C:\Users\Desktop\Outlook Files\Pre-Completion.txt" '< --- .txt file path
ElseIf Me.Option2 = True Then
   fn = "C:\Users\Desktop\Outlook Files\Post-Completion.txt" '< --- .txt file path
ElseIf Me.Option3 = True Then
   fn = "C:\Users\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
         [COLOR="Red"].List(i, 0) = Split(myArray(i), ",")(0)[/COLOR]
            .List(i, 1) = Split(myArray(i), ",")(1)
        Next i
      .ColumnWidths = "0pt;" & .Width - 4
    End With
End Sub
 
Last edited:

ByteMyzer

AWF VIP
Local time
Today, 09:38
Joined
May 3, 2004
Messages
1,385
You might check the value of myArray(i) at the point that the error is triggered. If the element contains no text, this means that the code is trying to parse an empty line at that position in the input text file.

For example, a file with:
Code:
Field1,Field2
1,One
2,Two

4,Four
...will fail on the line immediately following 2,Two.
 

Ranman256

Registered User.
Local time
Today, 12:38
Joined
Apr 9, 2015
Messages
3,594
if this is for Access, data is stored in tables.

if this is for Excel, you CAN load text data via arrays, but why not keep it in a sheet, and let the combo load from the sheet?
 

aman

Registered User.
Local time
Today, 09:38
Joined
Oct 16, 2008
Messages
1,250
How can we do the same thing if the data is stored in excel spreadsheet . I have to write outlook vba code as the form is in outlook .
 

aman

Registered User.
Local time
Today, 09:38
Joined
Oct 16, 2008
Messages
1,250
This is for outlook . The form is designed in outlook VBA .

In order to make it dynamic , I want to store data in a notepad and the listbox will get populated with the data stored in notepad.


if this is for Access, data is stored in tables.

if this is for Excel, you CAN load text data via arrays, but why not keep it in a sheet, and let the combo load from the sheet?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:38
Joined
Sep 21, 2011
Messages
6,360
Just walk through the code with F8 inspecting values where needed.
Set a breakpoint on the options and work from there.

What references do you need to get this to work?

I get error 458 on Get #ff, , txt
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom