View Full Version : [SOLVED] Need: VB code to get values from different closed workbooks

02-27-2014, 08:24 AM

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.


02-27-2014, 08:57 AM
hi. see if this helps: http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

02-27-2014, 08:59 AM
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

02-27-2014, 09:51 AM
Many thanks my dear friend
This code was really great and it solved the issue.

02-28-2014, 07:18 AM
you are welcome.