Read values from closed workbook?

[gfcaim]

Is it possible for a macro in Workbook_A.xls that is open to read values in Workbook_B.xls that is closed? Both workbooks are stored in the same directory.

I know it can be doen by linking but this is not what i need.

If so could someone please show me an example.

[tinyjack]

Function GetMax(strMatrix As String) As Long

Dim strResult As String
Dim strPullText As String

'If we get an error in this function it is because the sheet does not exist
On Error GoTo NoPage

'Build the string to pull the value from the closed file
strPullText = "'" & filePATH & "[" & fileMATRIX & "]" & Trim(strMatrix) & "'!R3C2"

'Pull the value from the closed file
GetMax = Application.ExecuteExcel4Macro(strPullText)

Exit Function

NoPage:

'There was an error so return the fact that the matrix is missing
GetMax = 0
Err.Clear

End Function

You will need to tailor this to suit and adjust the error handling.

filePATH and fileMATRIX are constants that I have setup in my project.

TJ

[gfcaim]

Thanks - works, but asks to open the workbook on each value. I am looking at loading a array of dimensions 5000x2!

Any other ideas?

[tinyjack]

You could have a look here:

http://j-walk.com/ss/excel/tips/tip82.htm

But I would open the workbook, get the array and then close the workbook.

TJ