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.

Sorry, wrong group, I'll post this in the Programming group.

"Wes" wrote:

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.