I am using the code below to retrieve a number from a closed workbook. The MsgBox returns the correct value but I am unable to assign that number to my modelVersion variable and don't understand why this is the case. I would appreciate if someone can help me out as I figure that the ExecuteExcel4Macro method maybe can't be used in this way?
Public Sub checkModelVersion()
Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
wbPath = "C:\mypath\"
wbName = "Update.xlsm"
wsName = "Dashboard"
cellRef = "E7"
Ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, -4150)
MsgBox ExecuteExcel4Macro(Ret)
modelVersion = ExecuteExcel4Macro(Ret)
End Sub