vb.net - 使用vb在特定文件夹下查找excel中的行数的脚本

Question

我需要找出文件夹下每个 excel 表中的行数。谷歌搜索显示下面的脚本有效..但是对 vb 的了解很少，我无法解决它。脚本包含''Wscript object.I认为这也适用于这个对象

事情在“c:\temp”下，我有100个excel表(.xls)。需要找出每个文件中的行数。需要 vb 专家的帮助

 Dim objFSO, strFolder, objFolder, objFile, objExcel, objSheet, objRange, objRows As Object

        Dim strExtension As String
        Dim V_FilePath As String = " "

        ' Specify folder.
        strFolder = "c:\\temp"    -----

        objExcel = CreateObject("Excel.Application")

        ' Enumerate files in the folder.
        objFSO = CreateObject("Scripting.FileSystemObject")
        objFolder = objFSO.GetFolder(strFolder)

        For Each objFile In objFolder.Files
            ' Select only Excel spreadsheet file.
            strExtension = objFSO.GetExtensionName(objFile.Path)

            If (strExtension = "xls") Or (strExtension = "xlsx") Then
                ' Open each spreadsheet and count the number of rows.
                objExcel.Workbooks.Open(objFile.Path)
                objSheet = objExcel.ActiveWorkbook.Worksheets(1)
                objRange = objSheet.UsedRange
                objRows = objRange.Rows

      ' Display spreadsheet name and the number of rows.

                MsgBox(objExcel.ActiveWorkbook + CStr(objRows.Count))
                ''Wscript.Echo(objFile.Path & " (" & objRows.Count & ")")

 ' Close the spreadsheet.

                objExcel.ActiveWorkbook.Close()
            End If

        Next

        ' Clean up.
        objExcel.Application.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

Answer 1

确保您使用“Sub _ ()”在顶部声明子例程。此外，我认为有几件事在语法上是不正确的。试试这个:

Sub blah()
Dim objFSO, strFolder, objFolder, objFile, objExcel, objSheet, objRange, objRows As Object

        Dim strExtension As String
        Dim V_FilePath As String

        V_FilePath = " "
        ' Specify folder.
        strFolder = "c:\\temp"

        objExcel = CreateObject("Excel.Application")

        ' Enumerate files in the folder.
        objFSO = CreateObject("Scripting.FileSystemObject")
        objFolder = objFSO.GetFolder(strFolder)

        For Each objFile In objFolder.Files
            ' Select only Excel spreadsheet file.
            strExtension = objFSO.GetExtensionName(objFile.Path)

            If (strExtension = "xls") Or (strExtension = "xlsx") Then
                ' Open each spreadsheet and count the number of rows.
                objExcel.Workbooks.Open (objFile.Path)
                objSheet = objExcel.ActiveWorkbook.Worksheets(1)
                objRange = objSheet.UsedRange
                objRows = objRange.Rows

      ' Display spreadsheet name and the number of rows.

                MsgBox (objExcel.ActiveWorkbook + CStr(objRows.Count))
                ''Wscript.Echo(objFile.Path & " (" & objRows.Count & ")")

 ' Close the spreadsheet.


            objExcel.ActiveWorkbook.Close

            End If

        Next

        ' Clean up.

        objExcel.Application.Quit

        Dts.TaskResult = ScriptResults.Success
    End Sub