CODEWITHSUNDEEP
excelfilevbatextimport
user1750557
user1750557

Reputation:

How to open multiple text files into one excel sheet but skip line 1 for each text file?

With the code below I am able to open all text files in my folder into one excel sheet. Each file has headers and what I cant figure out is how to have it skip the first line in each text file.

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("\\mydirectoryfolderhere")
' 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
For i = 0 To UBound(Items)
cl.Offset(1, i).Value = Items(i)
Next
'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

Upvotes: 2

Views: 1635

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Something like this (untested)

Dim firstline as boolean
'.....
For Each file In folder.Files
Set FileText = file.OpenAsTextStream(ForReading)
firstline=True

Do While Not FileText.AtEndOfStream
    TextLine = FileText.ReadLine

    If Not firstline then

        Items = Split(TextLine, "|")
        For i = 0 To UBound(Items)
            cl.Offset(1, i).Value = Items(i)
        Next
        Set cl = cl.Offset(1, 0)

    End If

    firstline=False
Loop

' Clean up
FileText.Close
Next file
'....

Upvotes: 1

Related Questions