I am traying to get value from a closed workbook via the following function :
Public Function GetValueFromClosedWorkbook(FileName As String, Sheet As String, CellAddress As String)
'?GetValueFromClosedWorkbook("C:\temp\excel partners.xlsx", "Excel", "B2")
Dim strFilePath As String, strFileNameShort As String, strArg As String
strFilePath = Left(FileName, InStrRev(FileName, "\"))
strFileNameShort = Right(FileName, Len(FileName) - InStrRev(FileName, "\"))
strArg = "'" & strFilePath & "[" & strFileNameShort & "]" & Sheet & "'!" & _
Range(CellAddress).Range("A1").Address(, , xlR1C1)
GetValueFromClosedWorkbook = ExecuteExcel4Macro(strArg)
End Function
Anyway it always return #VALUE!, please advsie where i am wrong as i couldnt find an answer on the web :)
check internet forums and similar codes
