vba - 如何在VBA中获取CSV文件中数据的行数

Question

我试图在 VBA 中获取几个 CSV 文件中的数据行数。这是代码。

Sub Woo_Products()

Dim fso As New FileSystemObject
Dim flds As Folders
Dim fls As Files
Dim strText As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim extfind As String
Dim FilePath As String
Dim sLineOfText As String

On Error Resume Next

Workbooks.Open Filename:="F:\Work\scrape\" & "woocommerce-products.csv", UpdateLinks:=3

Set fls = fso.getfolder("C:\Users\star\Downloads").Files
k = 2
For Each f In fls
    strText = f.Name
    extfind = Right$(strText, Len(strText) - InStrRev(strText, "."))
    If extfind = "csv" Then
        FilePath = "C:\Users\star\Downloads\" & strText

        Open FilePath For Input As #1
        i = 0
        Do Until EOF(1)
            Line Input #1, sLineOfText
            If sLineOfText <> "" Then i = i + 1
        Loop
        Close #1

    End If
Next

Windows("woocommerce-products.csv").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

但是我得到的每个文件的计数都是一样的。当然，每个文件都有不同的数据行。希望对此有所帮助。

Answer 1

如果您只需要行数，我会编写一个函数来返回行数。

Function getFileLineCount(FullFileName As String, Optional LineDelimiter As String = vbNewLine) As Long
    Dim text As String
    Dim fileNo As Integer, n As Long
    fileNo = FreeFile
    Open FullFileName For Input As #fileNo
    Do Until EOF(1)
        Line Input #1, text
        n = n + 1
    Loop
    Close #fileNo
    getFileLineCount = n
End Function