Count rows in every XLSX
Count rows in every XLSX
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:
CODE --> VBS
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 Else ' 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. objExcel.ActiveWorkbook.Close End If Next ' Clean up. objExcel.Application.Quit
Thanks a lot for any help!