Mixture of password protected and non password protected files

[bajwali]

HI

I have a bunch of files some password protected some not password protected. The password for those that are password protected is the same. My macro first asks the user to choose the files to load and then i use a loop to open them one by one and copy some data into my aggregator file.

Now the code below works however when i use an input box as shown in second code below it fails and excel vba appears very random sometime working for a few files and other times not. Could someone please educate me why this is happening.

Code that works

Sub Aggregator()
Dim FileNames As Variant, i As Integer, j As Integer
Dim UserRange As Range, DefaultRange As String
Dim TWB As Workbook, nr As Integer, aWB As Workbook

Set TWB = ThisWorkbook
Application.ScreenUpdating = False
MsgBox ("Enter Files that you wish to import data from")
FileNames = Application.GetOpenFilename(FileFilter:="Excel Filter (*.csv), *csv", Title:="Open File(s)", MultiSelect:=True)

For i = 1 To UBound(FileNames)
Workbooks.Open FileNames(i), Password:="Password"
 
More code here ...

Next i
    
End Sub

Code that is volatile and sometime works for a few files and other times fails out right

Sub Aggregator()
Dim FileNames As Variant, i As Integer, j As Integer
Dim UserRange As Range, DefaultRange As String
Dim TWB As Workbook, nr As Integer, aWB As Workbook
Dim c as String

Set TWB = ThisWorkbook
Application.ScreenUpdating = False
MsgBox ("Enter Files that you wish to import data from")
FileNames = Application.GetOpenFilename(FileFilter:="Excel Filter (*.csv), *csv", Title:="Open File(s)", MultiSelect:=True)
c=Inputbox("Password to open files")


For i = 1 To UBound(FileNames)
Workbooks.Open FileNames(i), Password:="c"
 
More code here ...

Next i
    
End Sub

 

[AlphaFrog]

Remove the quotes from around the "c". The quotes meant the password is the actual letter and not the variable from the inputbox.

Change this...
Workbooks.Open FileNames(i), Password:="c"

To this...
Workbooks.Open FileNames(i), Password:=c

 

[bajwali]

Hi @AlphaFrog

Thanks that is great and explains what was happening.

As a follow up this works perfectly for the files which are protected but fails when the loop comes to those files which are saved without a password on them. I get the following error

"Run time error 9 Subscript out of range" is their any way to get around that and should that be happening?

Thanks

B

 

[AlphaFrog]

On Error Resume Next
Workbooks.Open FileNames(i), Password:=c
If Err.Number <> 0 Then Workbooks.Open FileNames(i)
On Error GoTo 0

 

[bajwali]

Thanks @AlphaFrog that is brilliant man