Excel VBA如何从文本文件中一次读取5行数据，直到文件末尾？

I need help with VBA. I would like to read in ONLY 5 lines of data at a time, process the data, ClearContents and repeat until the end of the file.

我需要VBA的帮助。我想一次只读取5行数据,处理数据,ClearContents并重复直到文件结束。

This is what I have written but it is not working:

这是我写的,但它不起作用:

Dim FilePath As String, Dim Start As Integer

FilePath = "C:\Users\Main\temp3.txt"

Open FilePath For Input As 3
row_number = 5

Do Until EOF(3)

For Start = 1 To 5  

    Line Input #36, LineFromFile
    LineItems = Split(LineFromFile, ",")

    Range("A2").Value = LineItems(0)
    Range("B2").Value = LineItems(1)
    Range("C2").Value = LineItems(2)
    Range("D2").Value = LineItems(3)
    Range("E2").Value = LineItems(4)
    Range("F2").Value = LineItems(5)
    Range("G2").Value = LineItems(6)

    row_number = row_number + 1  

Next Start

'Process data here

Range("ClearContents").ClearContents

Loop

Close #3

2 个解决方案

#1

Something like this should work (untested)

像这样的东西应该工作(未经测试)

Sub Tester()

    Const BLOCK_SIZE = 5
    Const START_ROW As Long = 2

    Dim FilePath As String
    Dim LineFromFile As String, arr
    Dim rowNum As Long, lineNum As Long

    FilePath = "C:\Users\Main\temp3.txt"

    Open FilePath For Input As #1
    rowNum = START_ROW
    lineNum = 0

    Do Until EOF(1)

        Line Input #1, LineFromFile
        lineNum = lineNum + 1

        arr = Split(LineFromFile, ",")

        ActiveSheet.Cells(rowNum, 1).Resize(1, 7).Value = arr

        If lineNum = BLOCK_SIZE Then
            'Process data here
            Range("ClearContents").ClearContents
            rowNum = START_ROW
            lineNum = 0
        Else
            rowNum = rowNum + 1
        End If

    Loop

    Close #3

End Sub

#2

Try to use Thisworkbook.sheets("<nameofsheet>").Range("A2").value or Thisworkbook.sheets(<indexofsheet>).Range("A2").value instead of Range("A2").value.

尝试使用Thisworkbook.sheets(“ ”)。范围(“A2”)。value或Thisworkbook.sheets( )。范围(“A2”)。值而不是范围(“A2”)。值。

#1