Load array from closed workbook range

Hi all,

I am looking to load an array from a range in a closed workbook - that is, a workbook that must remain closed, nor can it be manipulated in anyway.

I can do this using the following code:

 mydata = "='C:\DirectoryName\[FileName.xls]SheetName'!A1:Z50"  
 With ActiveSheet.Range("A1:Z50")
 .Formula = mydata
 .Value = .Value
 End With

However, this simply inserts the data into a worksheet. I need it to go into an array.

How would this be done?