problem with a macro reading from a spreadsheet

[veggiesaregood]

I have written a VBA macro that reads values from spreadsheets in
closed workbooks as part of its routine. The macro works fine,
however, the spreadsheets being read are automatically generated by a
MATLAB application. That application basically writes tab delimited
data into a .xls file.

My problem is this. If I run the macro after the spreadsheets have
been generated, the macro will get a #REF error anytime it tries to
read a value from any of the spreadsheets. However, if I simply copy
all the values in the spreadsheet and paste them into a blank
spreadsheet and then save this new spreadsheet to overwrite the old one
then the macro can read all of the values without any problem

Basically, I am trying to figure out what is wrong with the formatting
of the data in the automatically generated spreadsheets that causes the
macro to be unable to read them. I am confused because copying and
pasting the data solves the problem.

All the data is numbers.

Thanks a lot!

 

[veggiesaregood]

By the way, this is the function that reads the value, so it is where
the #REF value gets read in the case where it is reading from the
automatically generated spreadsheet.

Private Function GetValue(Path, File, Sheet, Ref)
' Read a value from a closed workbook
Dim sTEMPLATE As String
Dim sSEP As String
Dim sArg As String
Dim arg As String

sTEMPLATE = "'&P[&F]&S'!&R"
sSEP = Application.PathSeparator
If Right(Path, 1) <> sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Exit Function
End If

arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Ref).Range("A1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function