How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening and it should give result on separate workbook.) is it possible using excel vba macro?
As far as I know, you can´t check if an excel file is password protected without opening the file. But a macro can open workbooks in a folder, check if password protected and then close the workbook. One by one.
Dim Value As String, a As Single
myfolder = .SelectedItems(1) & "\"
Range("C4") = myfolder
Range("B7:C" & Rows.Count) = ""
a = 0
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
If Err.Number > 0 Then
Range("C7").Offset(a, 0).Value = "Yes"
On Error GoTo 0
Range("B7").Offset(a, 0).Value = Value
a = a + 1
Value = Dir