tab delimited files and lsitboxes - not loading correctly.

[gman1979]

Hi guy's,

I’ve got a mini user form that writes to a tab delimited file. I’ve been looking to then load this into a listbox (for future editing use) but the listbox doesn’t seem to see the information as separate columns, I have one piece of code that loads it all into the listbox but as if all the info was in one column and I have another where I open the file in excel and try to load it in that way, this loads only the 1st column from the list.

Pretty new to tab delimited files and using them in excel so don't know if I’m missing anything out.

Below is the code that loads the file in multiple rows but in one column:

Private Sub CommandButton3_Click()
Dim textfile As String
Open "H:\My Documents\1.DAT" For Input As #1
Do While Not EOF(1)
Input #1, textfile
ListBox1.AddItem (textfile)
Loop
Close #1
End Sub


Below is the code that opened the file in excel then tries to load it into the listbox:

Private Sub CommandButton1_Click()
'--------------------------CHECK TEXTBOX1 IS POPUILATED---------------------------
If Me.TextBox1.Value = "" Then
MsgBox "PLEASE ENTER SOMETHING TO SEARCH FOR", vbExclamation
TextBox1.SetFocus
Exit Sub
End If
'--------------------------OPEN DAT FILE------------------------------------------
Workbooks.OpenText Filename:="H:\My Documents\1.DAT", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
ActiveWindow.WindowState = xlMinimized
Workbooks("1.DAT").Activate
'--------------------------ACTIVATE SHEET IN DAT FILE AND SEARCH------------------
Worksheets("1").Select
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF As String
Dim i As Integer
i = 0
Set rSearch = ActiveSheet.Range("c1", Range("c65536").End(xlUp))
strFind = Me.TextBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
FirstAddress = c.Address
Do
'Load details into Listbox
fndA = c.Offset(0, -2).Value
fndB = c.Offset(0, -1).Value
fndC = c.Value
fndD = c.Offset(0, 1).Value
fndE = c.Offset(0, 2).Value
fndF = c.Offset(0, 3).Value


MyArray(i, 0) = fndA
MyArray(i, 1) = fndB
MyArray(i, 2) = fndC
MyArray(i, 3) = fndD
MyArray(i, 4) = fndE
MyArray(i, 5) = fndF

i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
With Me.ListBox1
.Clear
'Load data into LISTBOX
.List() = MyArray
End With
ActiveWorkbook.Close SAVECHANGES:=False
End Sub

Any help whatsoever would be greatly appreciated

Thanks in advance

G

 

[jindon]

try

Private Sub CommandButton3_Click()
Dim fn As String, temp As String, delim As String
Dim x, y, a() As String, maxCol As Long, i As Long, ii As Long
fn = "H:\My Documents\1.DAT"
delim = vbTab
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(UBound(x), 100)
For i = 0 To UBound(x)
    y = Split(x(i), delim)
    For ii = 0 To UBound(y) : a(i, ii) = y(ii) : Next
    maxCol = Application.Max(maxCol, UBound(y))
Next
ReDim Preserve a(UBound(a, 1), maxCol)
With ListBox1
    .ColumnCount = maxCol
    .List = a
End With
End Sub

 

[gman1979]

thank you,

that worked great, is there a way to specify each columns width within the code you posted, for example if i know there are 5 columns, but want them to be different widths?

thanks again

G

 

[jindon]

If you know the width of each columns, Yes.

With ListBox1
    .ColumnCount = maxCol
    .ColumnWidths = "20;20;20;20;20"  <- change here
    .List = a
End With