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