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]

Hi Gerald,

you were mixing the row numbers of the array vNames with the row numbers of the sheet. I have expanded the array to include the column K which holds the passwords. Then you can work solely with the array:

Sub FilesClosewithPW()
'// Closes those workbooks that are listed in _
 // column E with passwords in column K
 
    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 = wsSheet.Range("E2:K14").Value  ' inclusive column with passwords
    '>>> Now filenames are in column 1 of vNames and passwords in column 6
    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.SaveAs Password:=vNames(iRow, 6) ' wsSheet.Cells(iRow, 11) <<< this won't work because sheet row numbers are different from vNames array row numbers
                    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

See the additional comments in the code

The reason to use an array is speed. Now in this case with 10 files or so it doesn't make a big difference. But if you ever need to read and or write to a few hundred or a few thousand cells, then the difference is enormous. Reads and writes from/to the sheet are relatively slow. So reading an entire range into an array (which then becomes like a mini sheet held in memory) is like reading a single cell. But working with the data (read or write) in the array is superfast. Then when you need to get the data back into the sheet, you only have one write (which for a large array can take a few seconds, but not much).

 

[gerald24]

Hi Gerald,

you were mixing the row numbers of the array vNames with the row numbers of the sheet. I have expanded the array to include the column K which holds the passwords. Then you can work solely with the array:

Sub FilesClosewithPW()
'// Closes those workbooks that are listed in _
 // column E with passwords in column K
 
    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 = wsSheet.Range("E2:K14").Value  ' inclusive column with passwords
    '>>> Now filenames are in column 1 of vNames and passwords in column 6
    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.SaveAs Password:=vNames(iRow, 6) ' wsSheet.Cells(iRow, 11) <<< this won't work because sheet row numbers are different from vNames array row numbers
                    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

See the additional comments in the code

The reason to use an array is speed. Now in this case with 10 files or so it doesn't make a big difference. But if you ever need to read and or write to a few hundred or a few thousand cells, then the difference is enormous. Reads and writes from/to the sheet are relatively slow. So reading an entire range into an array (which then becomes like a mini sheet held in memory) is like reading a single cell. But working with the data (read or write) in the array is superfast. Then when you need to get the data back into the sheet, you only have one write (which for a large array can take a few seconds, but not much).

Thanks sipjie for helping me with this! worked well. I just adjusted the below to (irow,7)

 wbFile.SaveAs Password:=vNames(iRow, 6)

 

[gerald24]

For some reason, during my usual working time, it saves to different folders. I suspect, it saves to the last active folder? for example, i saved another file to my personal drive or to my desktop, the files are being saved to the last folder with which a file was recently saved.
Is there a way to bypass this phenomenon?

 

[sijpie]

Don't understand why it is not saving the file to itsoriginal folder. But we can force that. try this (replace it in the original code):

    ...
            If wbFile.Name = vNames(iRow, 1) Then
                    wbFile.SaveAs Filename:=wbFile.FullName, Password:=vNames(iRow, 6) ' force to use the original filepath for saving
                    wbFile.Close savechanges:=False
                Exit For    ' no need to go through the rest of the list
            End If

   ....

 

[gerald24]

Don't understand why it is not saving the file to itsoriginal folder. But we can force that. try this (replace it in the original code):

    ...
            If wbFile.Name = vNames(iRow, 1) Then
                    wbFile.SaveAs Filename:=wbFile.FullName, Password:=vNames(iRow, 6) ' force to use the original filepath for saving
                    wbFile.Close savechanges:=False
                Exit For    ' no need to go through the rest of the list
            End If

   ....

this is perfect. thanks, man!