Open Close file with PW

[gerald24]

Hi Guys,

I have a main excel file that has a indirect sumif formula linked to different protected files.
Now, I want to open and close all files at the same time so that it would pull the correct sum without turning to #REF !
If I can turn the sumif formula to hard code values it would be better.

In the meantime below is the code that opens only (I need it closed too ). File directory is in column D, PW is on Column K.
Hope you can also help me to make it more sustainable? Like if I have new files to add, I dont need to go back to the codes and add in 1 by 1. Thank you so much!

Sub open_file()
Workbooks.Open Filename:=Range("D2"), Password:=Range("K2")
Workbooks.Open Filename:=Range("D3"), Password:=Range("K3")
Workbooks.Open Filename:=Range("D4"), Password:=Range("K4")
Workbooks.Open Filename:=Range("D5"), Password:=Range("K5")
Workbooks.Open Filename:=Range("D6"), Password:=Range("K6")
Workbooks.Open Filename:=Range("D7"), Password:=Range("K7")
Workbooks.Open Filename:=Range("D8"), Password:=Range("K8")
Workbooks.Open Filename:=Range("D9"), Password:=Range("K9")
Workbooks.Open Filename:=Range("D10"), Password:=Range("K10")
Workbooks.Open Filename:=Range("D11"), Password:=Range("K11")
Workbooks.Open Filename:=Range("D12"), Password:=Range("K12")
Workbooks.Open Filename:=Range("D13"), Password:=Range("K13")
Workbooks.Open Filename:=Range("D14"), Password:=Range("K14")
End Sub

 

[sijpie]

Sub FilesOpen()
'// Opens those workbooks that are listed in _
 // column D with PW in column K
 
    Dim iRow As Integer
    Dim wsSheet As Worksheet
    
    Set wsSheet = Sheets("Sheet1") '<<< modify the Sheet1 to the name you have your list of names on
    'by using the shSheet.Cells or shSheet.Range construction it does not matter which sheet is open _
     at the time that the macro is running. This helps building bug free code
    
    'Row 2 to x contain filenames in D and passwords in K
    For iRow = 2 To 14
        Workbooks.Open Filename:=wsSheet.Cells(iRow, 4), Password:=wsSheet.Cells(iRow, 11)
        ' alternative: Workbooks.Open Filename:=wsSheet.Range("D" & iRow), Password:=wsSheet.Range("K2" & iRow)
    Next iRow
End Sub

Sub FilesClose()
'// Closes those workbooks that are listed in _
 // column D
 
    Dim wbFile As Workbook
    Dim vNames As Variant
    Dim iRow As Integer, iUB As Integer
    
    'load the file names in an array for quick lookup
    vNames = Sheets("Sheet1").Range("D2:D14").Value '<<< modify the Sheet1 to the name you have your list of names on
    iUB = UBound(vNames, 1) ' get the size of the array (in case more names are added)
    
    For Each wbFile In Workbooks    'Loop through all open workbooks
        For iRow = 1 To iUB         'Find those that are in the list
            If wbFile.Name = vNames(iRow, 1) Then
                wbFile.Close savechanges:=False
                Exit For    ' no need to go through the rest of the list
            End If
        Next iRow
    Next wbFile
    
End Sub

 

[gerald24]

Hi Sipjie,

Thank you for taking the time looking at this. I can never come up with what you have shared with me.

First, I tried to Consolidate the 2 Sub Functions.

Sub Conso()
Call FilesOpen
Call FilesClose
End Sub

It debugs here (Renamed my sheet to Sheet1)

    vNames = Sheets("Sheet1").Range("D2:D14").Value '<<< modify the Sheet1 to the name you have your list of names on

Running FilesOpen works really well, however, individually running FilesClose, nothing happens.

Perhaps I am missing something in the process? Does is require to edit something in my file? I suppose it doesn't matter if my file name in Column D is in formula or hardcoded right?

 

[sijpie]

I don't understand why it debugs at this line. The only reason could be that the sheet name does not correspond. I suggest that you check by renaming the sheet to what you had originally (or some suitable name) and then copy/paste this name into the macros. That way you are sure it is the same.

I suppose it doesn't matter if my file name in Column D is in formula or hardcoded right?

How are your workbooknames listed? Just the name, or with extension (.xlsx)?

 

[gerald24]

I get it now. Since it goes to the last opened excel file, which does not have the file names, it debugs.

added the below.

vNames = workbooks("MacroFile.xlsm").Sheets("Sheet1").Range("E2:E14").Value '<<< modify the Sheet1 to the name you have your list of names on

Also, I tweaked the formula to E2:E14 (which does not included the file directory, only file names w/ extension) and it worked!!!


Thanks Sipjie! I'm all good now!

 

[gerald24]

Sorry to loop back to this. I just encountered something that might become a problem in the future.
Is there a way for the macro to put a note or something in my workbook if 1 or more files are not password protected?

 

[gerald24]

Sorry to loop back to this. I just encountered something that might become a problem in the future.
Is there a way for the macro to put a note or something in my workbook if 1 or more files are not password protected?

I saw this

Sub IsWorkbookProtected()
'PURPOSE: Determine if the ActiveWorkbook is password protected
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

With ActiveWorkbook
  If .ProtectWindows Or .ProtectStructure Then
    MsgBox "This workbook is password protected"
  Else
    MsgBox "This workbook is not password protected"
  End If
End With

End Sub

here: https://www.thespreadsheetguru.com/...orksheet-is-password-protected-with-excel-vba

But i can't figure how this can fit in the loop since if I activate the workbook, the macro will be lost in since it looks up in the column D of FilesOpen. Somehow i'm thinking if it can still lookup to column D, then offset maybe to (1,10).value = "Password protected", else offset(1,10).value = "Unprotected".

Note: I included Resume Next on Error and Display Alerts False (So that if file is missing, macro will continue to work)

Thanks in advance!

 

[gerald24]

I saw this

Sub IsWorkbookProtected()
'PURPOSE: Determine if the ActiveWorkbook is password protected
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

With ActiveWorkbook
  If .ProtectWindows Or .ProtectStructure Then
    MsgBox "This workbook is password protected"
  Else
    MsgBox "This workbook is not password protected"
  End If
End With

End Sub

here: https://www.thespreadsheetguru.com/...orksheet-is-password-protected-with-excel-vba

But i can't figure how this can fit in the loop since if I activate the workbook, the macro will be lost in since it looks up in the column D of FilesOpen. Somehow i'm thinking if it can still lookup to column D, then offset maybe to (1,10).value = "Password protected", else offset(1,10).value = "Unprotected".

Note: I included Resume Next on Error and Display Alerts False (So that if file is missing, macro will continue to work)

Thanks in advance!

this is not what I need. sorry. this only checks if sheet/workbook is protected but not actually checks if excel file is saved with a password.

 

[gerald24]

I tried tweaking the codes to the files with password. however, it jumbles the file with password from different rows.

Sub FilesClosewithPW()
'// Closes those workbooks that are listed in _
 // column D
 


 
    Dim wbFile As Workbook
    Dim vNames As Variant
    Dim iRow As Integer, iUB As Integer
    
    Dim wsSheet As Worksheet
     
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    
    Set wsSheet = Workbooks("Macrofile.xlsm").Sheets("Sheet1") '<<< modify the Sheet1 to the name you have your list of names on
    'by using the shSheet.Cells or shSheet.Range construction it does not matter which sheet is open _
     at the time that the macro is running. This helps building bug free code
    
    'load the file names in an array for quick lookup
    vNames = Workbooks("Macrofile.xlsm").Sheets("Sheet1").Range("E2:E14").Value '<<< modify the Sheet1 to the name you have your list of names on
    iUB = UBound(vNames, 1) ' get the size of the array (in case more names are added)
    
        'On Error Resume Next
    For Each wbFile In Workbooks    'Loop through all open workbooks
        For iRow = 1 To iUB         'Find those that are in the list
            If wbFile.Name = vNames(iRow, 1) Then
                    wbFile.SaveAs Password:=wsSheet.Cells(iRow, 11)
                    wbFile.Close savechanges:=False
                Exit For    ' no need to go through the rest of the list
            End If
        Next iRow
    Next wbFile
    
    
  Application.ScreenUpdating = True
Application.DisplayAlerts = True
    
End Sub

 

[sijpie]

I haven't got access to a computer till about 13th may. I will correct the code then