Unanswered: Getting data from closed books with VBA
This one may be a bit tricky, but I am hoping someone will know the answer.
I have used this Function that I got from a book to retrive values from closed books with VBA
Function GetValue(Path, File, Sheet, ref)
Dim Arg As String
Arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(ref).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(Arg)
It is basically building the formula for a lined cell, but when getting values in VBA it saves me having to open the book get the value and then close the book again. I would use a procedure like the one below to get it to pass back the value
dummy = getvalue("C:\work\","Accounts.xls","2005","A1")
What I would like to be able to do is pass it the row and column numbers rather than "A1"
Hence dummy = getvalue("C:\work\","Accounts.xls","2005",1,1)
would give me the same answer
Does anyone know what the "& Range(ref).Address(, , xlR1C1)" part of the function would need to be in order to accomplish this? Any help would be very much appreciated.