Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Count rows in every XLSX

Count rows in every XLSX

Count rows in every XLSX

Hi all,

this is my first post here and I am completely new to programming.
I hope anybody could give me a little help.
What I am trying to achieve is to build a VBS with the following functionality:

1. Inputbox, where the user has to provide a folder name, which is stored in specific path
2. Exit script, if clicked cancel
3. Get all names of every xlsx file in this folder including the amount of used rows in each file minus headline, so -1
4. Parse these information in a txt or better in a xlsx file formatted: <Name of xlsx sheet>;<Amount of rows -1>

What I found / tried before:


Option Explicit

Dim strInput

strInput = "\\mydomain.net\..\" & UserInput("Enter folder name: ")
WScript.Echo "You entered: " & strInput

'here how to do: if clicked cancel then quit?

Function UserInput( myPrompt )
' This function prompts the user for some input.
' When the script runs in CSCRIPT.EXE, StdIn is used,
' otherwise the VBScript InputBox( ) function is used.
' myPrompt is the the text used to prompt the user for input.
' The function returns the input typed either on StdIn or in InputBox( ).
' Written by Rob van der Woude
' http://www.robvanderwoude.com
    ' Check if the script runs in CSCRIPT.EXE
    If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
        ' If so, use StdIn and StdOut
        WScript.StdOut.Write myPrompt & " "
        UserInput = WScript.StdIn.ReadLine
        ' If not, use InputBox( )
        UserInput = InputBox( myPrompt )
    End If
End Function

' End of Input part here
' Start of counting rows in xlsx here

Dim objFSO, strFolder, objFolder, objFile, objExcel, objSheet, objRange, objRows
Dim strExtension

' Specify folder with variable from above with "strInput" ... doesn't work ... why?
strFolder = strInput

Set objExcel = CreateObject("Excel.Application")

' Enumerate files in the folder.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set 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
        Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
        Set objRange = objSheet.UsedRange
        Set objRows = objRange.Rows
        ' Display spreadsheet name and the number of rows.
        Wscript.Echo objFile.Path & " (" & objRows.Count & ")"

'here how to parse it into a txt or xlsx? with info: name of xlsx; amount of rows?

        ' Close the spreadsheet.
    End If

' Clean up.

Thanks a lot for any help!

Best regards,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close