使用VBA宏从文件夹中的txt文件头中删除2行

Question

你好，我正在使用以下宏来删除文件夹中文本文件的前两行。

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

在运行此脚本时，我遇到了类型不匹配的错误，出现在以下代码行： For Each fil In fld。

如果有人能帮助解决这个问题，将不胜感激。

- Sshitanshu

Answer 1

.GetFolder并不会执行你所想的操作。它返回的是一个文件夹对象。你需要获取文件夹内的文件。

尝试这样使用：

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

说实话，我不知道为什么你不使用更简单的Dir和*.txt文件掩码。

- user4039065

Answer 2

要循环遍历文件夹中的文件，请使用Jeeped建议的DIR。您可能想查看此stackoverflow链接

使用VBA循环遍历文件夹中的文件

使用Excel编写/操作文本文件是一个较慢的过程。这里有一个更快的过程。

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

如果您不想覆盖旧文件，则更改下面的行

    '~~> Delete old file
    Kill MyFile

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

我也假设文本文件中有两行以上。如果没有，那么你需要相应地处理。

- Siddharth Rout

Answer 3

正如Jeeped所回答的那样，使用Dir可能会更容易。 此外，用Excel打开它可以使操作对我来说稍微简单一些。

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

- Pierre44