Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-30-2018, 08:10 AM   #1
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,250
Thanks: 54
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Store data in multicolumn listbox from a text file

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
         .List(i, 0) = Split(myArray(i), ",")(0)
            .List(i, 1) = Split(myArray(i), ",")(1)
        Next i
      .ColumnWidths = "0pt;" & .Width - 4
    End With
End Sub


Last edited by aman; 11-30-2018 at 08:57 AM.
aman is offline   Reply With Quote
Old 11-30-2018, 11:08 AM   #2
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,385
Thanks: 0
Thanked 48 Times in 43 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Re: Store data in multicolumn listbox from a text file

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.
ByteMyzer is offline   Reply With Quote
Old 11-30-2018, 11:09 AM   #3
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,375
Thanks: 0
Thanked 746 Times in 731 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Store data in multicolumn listbox from a text file

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?

Ranman256 is online now   Reply With Quote
Old 11-30-2018, 01:40 PM   #4
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,250
Thanks: 54
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Re: Store data in multicolumn listbox from a text file

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 is offline   Reply With Quote
Old 12-03-2018, 03:05 AM   #5
aman
Newly Registered User
 
Join Date: Oct 2008
Posts: 1,250
Thanks: 54
Thanked 3 Times in 2 Posts
aman is an unknown quantity at this point
Re: Store data in multicolumn listbox from a text file

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.


Quote:
Originally Posted by Ranman256 View Post
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 is offline   Reply With Quote
Old 12-03-2018, 03:29 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Store data in multicolumn listbox from a text file

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 12-03-2018 at 03:56 AM.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multicolumn listbox append upon selection to new table komo Modules & VBA 3 10-24-2016 07:15 PM
Multicolumn listbox only showing 1 column of data jaccessable Forms 4 03-16-2016 09:42 AM
multiselect, multicolumn listbox homer2002 Modules & VBA 7 04-02-2013 09:00 AM
Handling multicolumn listbox row source TravelingCat Modules & VBA 8 07-28-2010 05:55 AM




All times are GMT -8. The time now is 03:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World