Solved: Extract Data From Text File

[MikeDube]

I'm pretty familiar with VBA however I have no experience with tab delineated files. I'm looking to pull information from a tab delineated text file into a multidimensional array. Is that possible to get done without having to read it onto the worksheet?

[GTO]

Greetings,

If the textfile is much in size, I would think that importing and snagging the range.value into an array would be quicker.

Anyways, not well tested, but appears to work:

Option Explicit

Sub exa()
Dim _
FSO As Object, _
fsoTxtFile As Object, _
aryAllVals As Variant, _
aryTmp As Variant, _
i As Long, _
x As Long, _
y As Long, _
lCols As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
'// Change fullname to suit. //
Set fsoTxtFile = FSO.OpenTextFile(ThisWorkbook.Path & "\TabDelim.txt")

With fsoTxtFile
i = i + 1
'// Get column and row count to size array with. //
lCols = UBound(Split(.ReadLine, vbTab, , vbTextCompare)) + 1
Do While Not .AtEndOfStream
i = i + 1
.SkipLine
Loop
.Close
End With

ReDim aryAllVals(1 To i, 1 To lCols)
Set fsoTxtFile = FSO.OpenTextFile(ThisWorkbook.Path & "\TabDelim.txt")

With fsoTxtFile
For x = 1 To UBound(aryAllVals, 1)
aryTmp = Split(.ReadLine, vbTab, , vbTextCompare)
For y = LBound(aryTmp) To UBound(aryTmp)
aryAllVals(x, y + 1) = aryTmp(y)
Next
Next
End With
'// To test...
Range("A1").Resize(UBound(aryAllVals, 1), UBound(aryAllVals, 2)).Value = aryAllVals
End Sub


Hope that helps,

Mark

[MikeDube]

Thank you very much I will use that as reference