GetValue from closed workbook

[Wes]

I've been using a function "GetValue" which gets a value from a closed
workbook. (Found at: http://j-walk.com/ss/Excel/tips/tip82.htm) Works great
except I want to put this into a loop to reference different cells.
Therefore I'm trying to change it so that it accepts TheRow and TheColumn as
arguments instead of ref which is in A1 notation.

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function


Thanks in advance for your help.

 

[Greg Wilson]

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