PDA

View Full Version : Solved: Delimiting in odd situations...



bdl004
07-21-2010, 05:17 AM
Hi Everybody,

I have a quick question about specifying delimiters when opening a text file for input. I know that when a text file is opened in excel that it is very easy to code delimiters.

My question is whether I can do this for reading a file in to an array in vba. I have attached the file that I would like to use for input. I would like the entire date in one element of an array, the entire time in another element, and the number in a third element.

If I simply use the "Input #1, gooddata" command, it only reads in the year, and the first number of the time. My code is also attached...

PS The bit about garbage is just to skip the top few lines...
Thanks!

EDGE
07-22-2010, 01:49 PM
I am sure there is an easier way of doing this but this gets the job done.

Sub ReadTextFile()
Dim ws As Worksheet
Dim oFSO As New FileSystemObject
Dim oFS
Dim x As Variant
Dim y As Variant
Dim xRow As Integer
Dim xCol As Integer
Dim Data() As String


Set oFS = oFSO.OpenTextFile("c:\TextFile.TXT")
Set ws = ThisWorkbook.Sheets("Sheet1")

xRow = 2
xCol = 1
IdxCnt = 0

Do Until oFS.AtEndOfStream
sText = oFS.ReadLine

If IsNumeric(Left(sText, 1)) Then ' Skips the header garbage
x = Split(sText, Chr(9)) ' Split data based on Tab
For i = 0 To UBound(x)
If x(i) <> "" Then
y = Split(x(i), " ") ' Split Date & Time
For a = 0 To UBound(y)
IdxCnt = IdxCnt + 1
ReDim Preserve Data(1 To IdxCnt) ' load array with all values
Data(IdxCnt) = y(a)
xCol = xCol + 1
Next a
End If
xRow = xRow + 1
Next i
End If

Loop

xRow = 2
xCol = 1
For h = 1 To UBound(Data)
ws.Cells(xRow, xCol).Value = Data(h)
xCol = xCol + 1
If xCol > 3 Then
xRow = xRow + 1
xCol = 1
End If
Next h

Set oFS = Nothing
Set oFSO = Nothing
Erase Data()


End Sub

bdl004
07-22-2010, 11:04 PM
Thanks for your reply! I'll see what I can do with this.