Mixture of password protected and non password protected files

bajwali

New Member
Joined
May 12, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
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

VBA Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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

New Member
Joined
May 12, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
VBA Code:
On Error Resume Next
Workbooks.Open FileNames(i), Password:=c
If Err.Number <> 0 Then Workbooks.Open FileNames(i)
On Error GoTo 0
 

Watch MrExcel Video

Forum statistics

Threads
1,129,662
Messages
5,637,637
Members
416,977
Latest member
kdoederlein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top