Mac Forum / Applications / Excel / March 2005

I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below.  It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet.  In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them.  However, if I try to cast the return
value as an integer I get a type mismatch error.  (example:  temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:

   Public Function GetValue(Path, File, Sheet, Ref) As Variant
      'Based on John Walkenbach's GetValue function:
      'http://www.j-walk.com/ss/excel /tips/tip82.htm
      Const sTEMPLATE As String = "'&P[&F]&S'!&R"
      Dim sSEP As String
      Dim sArg As String

      sSEP = Application.PathSeparator
      If Right(Path, 1) <> sSEP Then Path = Path & sSEP
      If Dir(Path & File) = "" Then
         GetValue = "File Not Found"
      Else
         With Application
           sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
             sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
             "&S", Sheet), "&F", File), "&P", Path)
         End With
         GetValue = ExecuteExcel4Macro(sArg)
      End If
   End Function

Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro.