Need: VB code to get values from different closed workbooks

[khaledocom]

Hi,

In the attached file, column "C" has texts refer to closed workbooks.
I need a code to get values from these closed workbooks as shown in the attached file.
Appreciate your urgent help and thanks in advance.


FormulaLink.xlsx

[mancubus]

hi. see if this helps: http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

[mancubus]

you can adopt it like this:

Option Explicit

'you can extract data from a closed file by using an
'XLM macro. Credit for this technique goes to John 
'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm 


Sub GetDataDemo()

    Dim FilePath$, FileName$, i&
    Const SheetName$ = "Form"

    Application.ScreenUpdating = False

    For i = 9 To Cells(Rows.Count, "C").End(xlUp).Row
        FilePath = Left(Cells(i, "C"), InStrRev(Cells(i, "C"), "\"))
        FileName = Mid(Cells(i, "C"), InStrRev(Cells(i, "C"), "\") + 1)
        Cells(i, "G") = GetData(FilePath, FileName, SheetName, "$C$73")
        Cells(i, "H") = GetData(FilePath, FileName, SheetName, "$E$42")
        Cells(i, "I") = GetData(FilePath, FileName, SheetName, "$E$73")
    Next i
End Sub


Private Function GetData(Path, File, Sheet, Address)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _    Range(Address).Range("A1").Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function

[khaledocom]

Many thanks my dear friend
This code was really great and it solved the issue.
Appreciate.

[mancubus]

you are welcome.