Hi,
This is my first question so hope this is correct place.
I really love using the Excel Data Consolidate tool under VBA control as it rapidly consolidates data from closed workbooks. This code below works perfectly:
Selection.Consolidate _
Sources:=Array("'" & FolderPath & "[" & WBname & ".xlsx" & "]" & tWSname(r) & "'!" & ConsRange), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
However I now want to consolidate from unopened pw protected files - "without having to type the pw each time". The key point here is that opening files first in the background with embedded pw in VBA code is a security risk if it crashes with the pw protected file open. The Assistant who runs the process is not authorised to see the full contents of the salary file. The Excel provided Data Consolidate pulls the data without opening the file in the background and so is also so much faster.
I expected to be able to simply add , Password:="password" as you would with file open but I cannot see how to apply it without the VBA editor wanting to debug.
Any ideas would be much appreciated.