I'm in need of a macro to retrieve data from a password protected workbook that's on SharePoint. The masterbook that I'm working on will retrieve the values from a sheet that's inside the protected workbook and set values of a sheet inside the masterbook accordingly. It needs to run without any user prompt and without opening the workbook.
I'm familiar with basics of VBA. Working with objects and such.
I can do this by opening the workbook but It's not what I need.
This is what I could find on Google.
Dim rgTarget As Range
Set rgTarget = ActiveSheet.Range("G2:H10") 'where to put the copied data.
rgTarget.FormulaArray = "='D:\[Product_Details.xlsx]Sheet1'!$B$4:$E$10"
rgTarget.Formula = rgTarget.Value
I'm not sure if it'd would work with a SP address and I'm not sure where to put the password.