get value/charts in another workbooks without open

Question

The title is clear.

I want to copy data (& charts) from closed workbooks to my current (opened) workbook.

It is easy to create a link between two workbooks, with the name of the cell, but the value doesn't update until I open the closed workbook. How can I update it easily without opening the workbooks ?

How can I generalise this to charts ?

Answer 1

I finally used these lines of code

Arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & "R4C4" 'Range(Ref.Range("A1").Address(, , xlR1C1))C
     'Execute XLM macro
    GetValue = ExecuteExcel4Macro(Arg)

Way more simple with some loops.