Hi everyone,
I have search a code that will get data from a closed workbook.
Here is the code:
Option Explicit
Private Function GetValueFromClosedWorkbook(path, file, sheet, ref)
Dim arg As String
' check whether the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValueFromClosedWorkbook = "File Not Found"
Exit Function
End If
'create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A2").Address(, , xlR1C1)
' execute an XLM macro
'All references must be given as R1C1 strings.
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValueFromClosedWorkbook()
Dim p As String, f As String
Dim s As String, a As String
p = ThisWorkbook.path
f = "File-A-Y1.xls"
s = "1"
a = "b2"
ActiveSheet.Range("F2") = GetValueFromClosedWorkbook(p, f, s, a)
End Sub
However this code will only get the value with the specified cell.
How could i manipulate this code to loop through a given "file names", "sheets name", "data columns" according to their particular ID code.
Attach are my sample files for easy understanding.
Thanks in advance.
Bookmarks