script to find number of rows in excel under a particular folder using vb

Question

I need to find out the number of rows in each excel sheets under a folder .Google search shows that the below script works..but having very less knowledge on vb ,i could not resolve it.The script containe ''Wscript object.I think this works with out this object as well

The thing is under "c:\temp", i have 100 excel sheets(.xls). Need to find out number of rows in each file. Help needed from vb experts

 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

This is working.in the sense i am getting the coounts. but i need each file name and count so how can i get these details — user1254579
– user1254579, Commented Aug 5, 2013 at 14:00

the-nick-wilson · Accepted Answer · 2013-08-05 13:24:39Z

2

Make sure you're declaring the sub routine at the top with "Sub _()". Also, there are a few things that I believe are syntactically incorrect about this one. Try this instead:

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

answered Aug 5, 2013 at 13:24

the-nick-wilson

6261 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1254579 Over a year ago

i got an errorError: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException: Operator '+' is not defined for type 'Workbook' and string "36". at Microsoft.VisualBasic.

user1254579 Over a year ago

if i type MsgBox ( CStr(objRows.Count) then it would work but i need to know the file name and corresponding count

the-nick-wilson Over a year ago

This honestly doesn't look like VBA. I don't think you can use a "+" operator to concatenate like that in VBA.

the-nick-wilson · Accepted Answer · 2013-08-05 15:27:31Z

If you're doing this in VBA in an Excel Macro, perhaps this will work a bit better:

Sub LoopThroughFiles()
    Dim strFile As String
    Dim strPath As String
    Dim colFiles As New Collection
    Dim i As Integer
    Dim rowCount As Integer

    strPath = "C:\Users\[windows_username]\Documents\" 'Your path here
    strFile = Dir(strPath)

    While strFile <> ""
        colFiles.Add strFile
        strFile = Dir
    Wend

    'List filenames in Column A of the active sheet
    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
            ActiveSheet.Cells(i, 1).Value = colFiles(i)

            Workbooks.Open strPath & colFiles(i)
            rowCount = ActiveSheet.UsedRange.Rows.Count
            Workbooks(colFiles(i)).Close
            'Workbooks.Close
            'ThisWorkbook.Close
            ActiveSheet.Cells(i, 2).Value = rowCount


        Next i
    End If

End Sub

user1254579 · Accepted Answer · 2013-08-05 14:23:59Z

0

This would work MsgBox(objFile.name + CStr(objRows.Count))

answered Aug 5, 2013 at 14:23

user1254579

4,04725 gold badges70 silver badges111 bronze badges

Collectives™ on Stack Overflow

script to find number of rows in excel under a particular folder using vb

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related