I have multiple text files in a directory (.hne files) and I want to import the data into Excel but only the data after row 11. The code below imports the data, however it can only do it for one file at a time and my text file has four columns separated by space when it imports to Excel it puts it all in one column.
Can someone please help me clean up this code I would surely appreciate it. Also, I have a lot text files and it would probably exceed the maximum row in one activesheet would it be possible that it automatically puts the rest of the data on another sheet.
Private Sub()
Dim FilePath As String
Dim i, j As Integer
i = 0
j = 1
FilePath = ThisWorkbook.Path & "\201412010030.hne"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(FilePath, 1)
Do Until objFile.AtEndOfStream
strline = objFile.ReadLine
'If objFile.AtEndOfStream = True Then
'MsgBox strLine
'End If
i = i + 1
If i > 11 Then
Cells(j, 1).Value = strline
j = j + 1
End If
Loop
objFile.Close
End Sub
Here is an example of the file im trying to import
TRIAXYS BUOY DATA REPORT - TAS05642
VERSION = 6a.02.08
TYPE = HNE
DATE = 2014 Dec 01 01:00
NUMBER OF POINTS = 2072
TIME OF FIRST POINT (s) = 90.63
SAMPLE INTERVAL (s) = 0.78
COLUMN 1 = TIME (s)
COLUMN 2 = HEAVE (m)
COLUMN 3 = DSP NORTH (m)
COLUMN 4 = DSP EAST (m)
90.63 -0.42 0.53 -0.34
91.41 0.14 0.72 -0.39
92.19 0.45 0.61 -0.47
92.98 0.75 0.26 -0.11
93.76 1.04 -0.26 0.53
94.54 0.58 -0.68 0.94
95.32 -0.13 -0.67 1.00
96.10 -0.26 -0.47 0.90
96.88 -0.50 -0.31 0.50
97.66 -0.94 -0.22 -0.14
This code below works really well, the only thing i need it do is import all the data after row 11.
Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range
' Get a FileSystem object
Set fso = New FileSystemObject
' get the directory you want
Set folder = fso.GetFolder("C:\hnefiles")
' set the starting point to write the data to
Set cl = ActiveSheet.Cells(1, 1)
' Loop thru all files in the folder
For Each file In folder.Files
' Open the file
Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
Do While Not FileText.AtEndOfStream
TextLine = FileText.ReadLine
' Parse the line into | delimited pieces
Items = Split(TextLine, " ")
' Put data on one row in active sheet
cl.Resize(1, UBound(Items) - LBound(Items) + 1).Value = Items
' Move to next row
Set cl = cl.Offset(1, 0)
Loop
' Clean up
FileText.Close
Next file
Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub
