Delete 2 lines from header in txt files in a folder using vba macro

Question

Hi im using the below macro to delete first 2 lines of txt files in a folder

Private Sub remove()

Dim FSO, txs, fld, fil As file, content, nLinesToSkip, i
Set FSO = CreateObject("Scripting.FileSystemObject")

nLinesToSkip = 2

fld = FSO.GetFolder("O:\New folder\")
For Each fil In fld
    If Right(fil.Name, 3) = "txt" Then

        Set txs = fil.OpenAsTextStream(1) ' 1 = for reading
        For i = 1 To nLinesToSkip
            txs.SkipLine
        Next i
        content = txs.ReadAll
        txs.Close

        Set txs = fil.OpenAsTextStream(2) ' 2 = for writing
        txs.Write content
        txs.Close

    End If
Next fil
End Sub

while running this script im getting type mismatch error for line For Each fil In fld

would appreciate if anyone can assist in solving this issue

score 1 · Answer 1 · answered Aug 07 '18 at 11:56

.GetFolder isn't doing what you think it is. It returns a folder object. You want the files within the folder.

Try it as,

Set fld = FSO.GetFolder("O:\New folder\")
For Each fil In fld.Files
    ...
Next fil

TBH, I don't know why you aren't using the simpler Dir with a *.txt file mask.

score 1 · Answer 2 · answered Aug 07 '18 at 13:01

To Loop through files in a folder use DIR as suggested by Jeeped. You may want to see this stackoverflow link

Loop through files in a folder using VBA

Writing/Manipulating Text file using Excel is a slower process. Here is a much faster process

Sub Sample()
    Dim MyData As String, strData() As String
    Dim MyFile As String

    MyFile = "C:\Users\routs\Desktop\Sample.Txt"

    '~~> Read the file in an array in 1 go
    Open MyFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    '~~> Delete old file
    Kill MyFile

    '~~> Write to new file
    Open MyFile For Output As #1

    '~~> From 3rd line onwards
    For i = 2 To UBound(strData)
        Print #1, strData(i)
    Next i

    Close #1
End Sub

If you do not want to overwrite the old file then change the below lines

    '~~> Delete old file
    Kill MyFile

    '~~> Write to new file
    Open MyFile For Output As #1

I am also assuming that there are more than 2 lines in the text file. If not then you will have to handle that accordingly.

score 0 · Accepted Answer · answered Aug 07 '18 at 12:16

As Jeeped answered, it might be easier with the Dir. Also, opening it with excel makes the operation slightly more simple to my sense.

Sub Test()

    Dim StrFile As String
    Dim WB As Workbook
    Dim K As Integer
    Set WB = ActiveWorkbook

StrFile = Dir("O:\New folder\*.txt")

    Do While Len(StrFile) > 0

       Workbooks.Open Filename:="O:\New folder\" & StrFile
       Application.DisplayAlerts = False
           Rows("1:2").Delete Shift:=xlUp
           ActiveWorkbook.SaveAs Filename:="O:\New folder\" & StrFile, FileFormat:=xlText, CreateBackup:=False
           ActiveWindow.Close
       Application.DisplayAlerts = True

       StrFile = Dir

    Loop


End Sub

Delete 2 lines from header in txt files in a folder using vba macro

3 Answers3