GetValue from closed workbook
Perhaps the following. I include two examples. You need to pass the full wb
path and file name to the GetData procedure. I use GetOpenFileName in the
examples. This does not open the source wb, just gets the path and name.
Note that, for example, if the destination range is ActiveSheet.Range(A1:J1)
and the source cell name is "J10" then the destination range (A1:J1) will be
populated with the source range values from J10:S10. Similarly, if the
destination range is ActiveSheet.Range(A1:A10) and the source cell name is
still "J10" then the destination range (A1:A10) will be populated with the
source range values from J10:J19.
Sub Test1()
Dim FileName As Variant
FileName = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FileName = False Then Exit Sub
GetData CStr(FileName), "Sheet1", "A1", ActiveSheet.Range("A1:A20")
End Sub
Sub Test2()
Dim FileName As Variant
FileName = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FileName = False Then Exit Sub
GetData CStr(FileName), "Sheet1", "A1", ActiveSheet.Range("A1:J1")
End Sub
Sub GetData(SourceFile As String, SourceSheet As String, _
SourceCell As String, DestRng As Range)
Dim P As String
P = Left$(SourceFile, InStrRev(SourceFile, "\") - 1)
SourceFile = Dir(SourceFile)
DestRng.Formula = "=If('" & P & "\[" & SourceFile & "]" & _
SourceSheet & "'!" & SourceCell & "=" & """"", """", '" & _
P & "\[" & SourceFile & "]" & SourceSheet & "'!" & SourceCell & ")"
DestRng.Value = DestRng.Value
End Sub
Greg
|