Solved: txt file to array to listbox

[dchapin]

I am very new to vba and am really having a pain figuring this out. I have a text file that looks like so:

Abstractor.Address1
Abstractor.Address1
Abstractor.Address1Address2
Abstractor.Address1Address2
Abstractor.Address2
Abstractor.Address2
Abstractor.City
Abstractor.City
Abstractor.CityStateZip
Abstractor.CityStateZip
Abstractor.Code
Abstractor.Code
Abstractor.ContactType
Abstractor.ContactType

These are simple lines with a line break after each one. The are about 35,000 lines in the file. I have the code below working to pull the text file contents directly into the listbox:

Open "C:\SPSelect\TheDans\myTextFile.txt" For Input As #1
Do While Not EOF(1)
Input #1, MyString
ListBox1.AddItem (MyString)
Loop
Close #1

I really want to pull the contents of the text file into an array (so I can use it later) and also populate the listbox.

I have no idea how to even get started with the array. I think that the array needs to be dynamic, from what I've read, and because it will not be a set number of fields.

Any help would be greatly appreciated.
Thanks

[MOS MASTER]

Hi & Welcome to VBAX D, :hi:

35,000 lines is alot for a simple text file and I do wonder how helpfull one listbox with that amount of records will be... (perhaps rethinking your design would help your end goal)

You didn't specify what type of listbox your using so for now I'm presuming it's a listbox on a userform.

In your code you read the textbox line for line. This could take a lot of time and it's more easy to read the textfile in one line of code. (with the LOF method)

As for creating the Array. There are to many ways to do this so I'll give you an efficient way to:
A: Read the whole contents of the text file in one string
B: Split that string (on vbCrLf) in one time into one big Array
C: Feed that Array in one line to the listbox by filling the List property of that control.

I have attached a sample document with the sample text file you've provided. (code is commented so you understand what's going on)

It has a module with a function for the text file and a sub to call the userform:
Option Explicit

' \\ Function to return the full content of a text file as a string
Public Function LoadTextFile(sFile As String) As String
Dim iFile As Integer

On Local Error Resume Next
' \\ Use FreeFile to supply a file number that is not already in use
iFile = FreeFile

' \\ ' Open file for input.
Open sFile For Input As #iFile

' \\ Return (Read) the whole content of the file to the function
LoadTextFile = Input$(LOF(iFile), iFile)

Close #iFile
End Function

' \\ Call Form
Sub ShowForm()
Load UserForm1
UserForm1.Show
End Sub
And in the Userform you'll find the following code to fill the listbox:
Option Explicit
Private Sub UserForm_Initialize()
Dim sPath As String
Dim aValues() As String

' \\ Path to text file
sPath = ThisDocument.Path & "\dummy.txt"
' \\ Fill array by splitting the text file
' \\ on paragraph mark line feed (vbCrLf)
aValues = Split(LoadTextFile(sPath), vbCrLf, -1, vbTextCompare)

' \\ Fill listbox
Me.ListBox1.List = aValues
End Sub
This should get you started and good luck!

HTH

[dchapin]

That got me runnin.... Hopefully nuthin will stop me now.... Yea right.

Dan

[MOS MASTER]

Hi Dan,

Glad I could help!
Please keep us posted and mark your thread solved when your conquest has ended! ;)

[fumei]

Just curious, as I also saw it in another thread.

Why do you use:

Load Userform1
Userform1.Show

????????

Is there a specific reason for using two instructions?

Userform1.Show loads the userform if it is not already loaded.

[MOS MASTER]

Hi Gerry, :hi:

Sure, I think you already know that the Load instruction (Pre-loads) an object before it's made visible. (in this case by the show method) to the user.

With pre-loading I mean that the Initialize method of the userform class is triggered and therefore pre-processing is possible.

I'm used to using it for various reason's I'll give you some:
A: pre-processing (in case of show/hiding controls or pre-loading data in controls)
B: If you have a long running code you could have the load instruction in the beginning, do other stuff in the middle and use the Show method to present the dialog immediately (or A case where you preload an object at start-up for instance and have the Show command under a button to Show the dialog without having to preload it)

So in some scenarios it has great benefits to use it this way. (it could have in this scenario as well because we are talking about 35,000 records that need loading... if we agree with this or not) ;)

And of course it is a matter of preference as well.

[fumei]

In this case, fair enough.

[MOS MASTER]

Glad to hear that Gerry! :D

[fumei]

Except for one thing....

Load....
Show

There is NO advantage, as there is, in fact no pre-Load.

Load
Show

It loads...and immediately after...Shows.

Show

It loads and shows.

I can see an advantage if you:

Load
...do something else
Show

But:

Load
Show

is, to me, vitually the same as:

Show

because Show does Load.

In the case of:

Load Userform1
Userform1.Show

neither your A, or your B applies.

[MOS MASTER]

Hi Gerry,

Where the years went....:rofl: (I've missed this)

It is preloading because on Load the intialize of the Userform is executed and therefore when you call .Show the intialize method isn't run anymore. (if you call only Show without load it will initialize as well)

I agree however that in the example I gave there is no direct advantage because the lines execute after another. (has been stated before)

I've explained to you why one would use Load. I also explained the point you have as:


I can see an advantage if you:

Load
...do something else
Show


And I said it could have in the above scenario as well. (In which I meant if the OP finished his code he could do something like I said in: (or A case where you preload an object at start-up for instance and have the Show command under a button to Show the dialog without having to preload it) which is somewhat the same as your quote.

And I also explained that it's a matter of preference to use it yes or no.

So yes strictly speaking in the example code I gave (using Load and Show beneath eachother) you are right. But as I explained above it's not the intented use of the code.

[MOS MASTER]

Dan, is this one solved or do you have more questions about this one?

[dchapin]

Thanks, not sure how to mark it solved...

[MOS MASTER]

Hi, :yes

See my signature it's in the Thread Tools of this thread.

And your welcome of course. ;)