Macro to Retrieve Values From A Password Protected Workbook on SharePoint Without Opening It

%3CLINGO-SUB%20id%3D%22lingo-sub-3397241%22%20slang%3D%22en-US%22%3EMacro%20to%20Retrieve%20Values%20From%20A%20Password%20Protected%20Workbook%20on%20SharePoint%20Without%20Opening%20It%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3397241%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20relatively%20new%20to%20VBA%20so%20please%20bear%20with%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20in%20need%20of%20a%20macro%20to%20retrieve%20data%20from%20a%20password%20protected%20workbook%20that's%20on%20SharePoint.%20The%20masterbook%20that%20I'm%20working%20on%20will%20retrieve%20the%20values%20from%20a%20sheet%20that's%20inside%20the%20protected%20workbook%20and%20set%20values%20of%20a%20sheet%20inside%20the%20masterbook%20accordingly.%20It%20needs%20to%20run%20without%20any%20user%20prompt%20and%20without%20opening%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20familiar%20with%20basics%20of%20VBA.%20Working%20with%20objects%20and%20such.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20do%20this%20by%20opening%20the%20workbook%20but%20It's%20not%20what%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20could%20find%20on%20Google.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Dummy()%0ADim%20rgTarget%20As%20Range%0ASet%20rgTarget%20%3D%20ActiveSheet.Range(%22G2%3AH10%22)%20'where%20to%20put%20the%20copied%20data.%0ArgTarget.FormulaArray%20%3D%20%22%3D'D%3A%5C%5BProduct_Details.xlsx%5DSheet1'!%24B%244%3A%24E%2410%22%0ArgTarget.Formula%20%3D%20rgTarget.Value%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20if%20it'd%20would%20work%20with%20a%20SP%20address%20and%20I'm%20not%20sure%20where%20to%20put%20the%20password.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'd%20greatly%20appreciate%20your%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3397241%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

Hello,

 

I'm relatively new to VBA so please bear with me.

 

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.

 

Sub Dummy()
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
End Sub

 

I'm not sure if it'd would work with a SP address and I'm not sure where to put the password.


I'd greatly appreciate your assistance.

 

Thank you.

 

 

 

 

 

 

 

 

0 Replies