vba code to count rows in multiple .csv files

Litigation Support Tip of the Night is Five Years Old! Five years of tips each night since 2015.

Tonight, I tested out the below vba code posted here by mdmackillop which allows you to generate counts of the number of rows in multiple .csv files.

As always, simply press ALT + F11 to go into Visual Basic and enter this code in a new module for the workbook. List the folder containing your source files on the line beginning: sPath = "

Be sure not to use any other applications on your PC while this macro is running, or it may stop and give you incomplete results.

When it finishes it will generate a list of each source .csv file and show the total row count in an adjacent column.

As always, I confirmed the accuracy of this vba code tonight working with real data.

Sub OpenCSVFiles()

Dim wb As Workbook, wbCSV As Workbook

Dim sPath As String, sFilename As String

Dim NbRows As Long, rg As Range

Set wb = ThisWorkbook

Application.ScreenUpdating = False

sPath = "C:\foofolder5\" '\ added to correct file path

sFilename = Dir(sPath & "*.csv")

Do While Len(sFilename) > 0

Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file

NbRows = wbCSV.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'maximise rows to check; 100 may be exceeded

Set rg = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'maximise result rows; 100 may be exceeded

rg = sFilename

rg.Offset(0, 1) = NbRows

wbCSV.Close False 'close file

sFilename = Dir

Loop

Application.ScreenUpdating = True

End Sub

Highlighting Cells in a Range with Data Different from the Top Row

Don't miss that you can easily find where differences in a range of columns or rows appear in an Excel worksheet. If you have a worksheet where most of the data repeats in each row in a column, and you just want to detect where the data varies from the entries in the beginning of the range, you can do so using Excel's Find & Select tool.

In this example, we have a worksheet in which most of the entries in three columns of data match the entry in the first row.

1. Select the range below the column headings and copy the data to the clipboard.

2. Go to Home . . . Find & Select . . .Go to Special

3. Select the column differences radio button, and click OK.

4. Excel will highlight only the differences in each column where cells differ from the entries in the top row.

VBA code to unprotect multiple workbooks with the same password

The Visual Basic code posted here, by the great Graham Mayor, can be used to either automatically change the password for multiple Excel files or change their password to something other than the original password.

Open a blank workbook, and then press ALT + F11 to go into Visual Basic. In a new module enter the below VBA code. This macro will enter a given password for all of the Excel files with the extension .xls or xlsx in the file path listed on the line beginning:

Const fPath As String =

Enter the current password for the files on the line beginning: Const strPassword As String =

. . . and then the new password on these lines:

Password:="", _ WriteResPassword:="", _

If you don't want the files to be password protected you can simply enter "" on these lines.

In this example the original password of the files is: desk

. . . and I am unprotecting the workbooks altogether.

Sub RemovePasswords() Dim xlBook As Workbook Dim strFilename As String Const fPath As String = "C:\FooFolder\excels\" 'The folder to process, must end with "\" Const strPassword As String = "desk" 'case sensitive Const strEditPassword As String = "" 'If no password use "" strFilename = Dir$(fPath & "*.xls") 'will open xls & xlsx etc While Len(strFilename) <> 0 Application.DisplayAlerts = False Set xlBook = Workbooks.Open(Filename:=fPath & strFilename, _ Password:=strPassword, _ WriteResPassword:=strEditPassword) xlBook.SaveAs Filename:=fPath & strFilename, _ Password:="", _ WriteResPassword:="", _ CreateBackup:=True xlBook.Close 0 Application.DisplayAlerts = True strFilename = Dir$() Wend End Sub

Press play and the macro will open each file and remove the original password. One of the nice things about Graham's code is that it creates a back-up of each original file, which retains the original password, and is renamed with 'Backup of' at the beginning of its file name.

As always, I have tested out this vba code and confirmed that it works.

Note if you have trouble pasting this vba code directly into Visual Basic from this page, copy it into Word first and then re-paste into Visual Basic.