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.
Sub CheckWbook() Dim Value As String, a As Single With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With Range("C4") = myfolder Range("B7:C" & Rows.Count) = "" a = 0 Value = Dir(myfolder) Do Until Value = "" If Value = "." Or Value = ".." Then Else 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" End If Workbooks(Value).Close False On Error GoTo 0 Range("B7").Offset(a, 0).Value = Value a = a + 1 End If End If Value = Dir Loop End Sub