csv to multi-column listbox

[dvenn]

Hi everyone.. I've searched for a solution that would be close to what I am needed, however So far I have not come up with one.

I have a CSV text file, that I would like to load into a multicolumn list box (3 to 4 columns with headers).

Currently I can load the text file and using trim and mid I can get it to load into the columns however the text in the file is not standardized in regards to length, so I need to use the comma as a delimiter.

thanks in advance.

[Bob Phillips]

Is this what you mean



Dim oData As Workbook
Dim aryData

Set oData = Workbooks.Open("C:\test\Family.csv")
With UserForm1
aryData = oData.Worksheets(1).Range("A1").CurrentRegion
.ListBox1.List = aryData
.ListBox1.ColumnCount = 4
.Show
End With

[dvenn]

Not exactly.. I should have posted the code I currently have.. My apologies for that.


Sub fill_listbox()
Const myFile As String = "k:\testing\test.txt"
Dim FileNum As Integer, myStr As String
Dim mylines As Long


' my feeble attempt to collect a dynamic array using another procedure

mylines = LinesInFile(myFile)

Dim myArray() As String
ReDim myArray(mylines - 1, 3)
Dim myRow As Long

myRow = 0
FileNum = FreeFile ' next file number

'populate usefrom's listbox | does not include header

Open myFile For Input Access Read Shared As #FileNum ' open the file for reading
Do While Not EOF(FileNum)
Line Input #FileNum, myStr ' read a line from the text file
myArray(myRow, 0) = Trim(Mid((myStr), 1, 10)) ' would love to use the comma as a delimter versus counting characters.
myArray(myRow, 1) = Trim(Mid((myStr), 11, 10))
myArray(myRow, 2) = Trim(Mid((myStr), 22, 8))
myArray(myRow, 3) = Trim(Mid((myStr), 31, 12))

myRow = myRow + 1

Loop ' until the last line is read
Close #FileNum ' close the file
ListBox1.List = myArray
End Sub




Hope this helps with where my mind was going.

[Bob Phillips]

Have you tried my suggestion (it assumes a CSV as you said), if so, what does it do/not do?

[Norie]

Daniel

If you want to use a delimiter use Split.

PS There's no way you'll be able to create 'real' headers in the listbox if you populate it this way.

You can only have headers when you populate from a worksheet range.;)