Вообще по-моему в этом случае оптимальнее использовать формулы (вручную или через VBA), но если очень хочется, то см. цитату ниже.

KL
[MVP - Microsoft Excel]

------------- http://www.j-walk.com/ss/excel/eee/eee009.txt ------------

Created by Bob Umlas and adaptation to a Function procedure
by John Walkenbach

This procedure allows for the access of information in a closed workbook
with VBA.

From Bob:

Here's a routine you can incorporate into your programs if you ever have
a need to retrieve data from a file without opening it. In the needed case,
I had to read any number of files (depending on user's selection from a
listbox) and determine whether range W11:W36 on sheet "General"
totalled zeroand do one thing if ANY file met that condition or another
thing if not.

At first, I thought I'd need to open each file, take the sum, then close
it again.

Not true.

By building a string which you can pass into the ExecuteExcel4Macro, you
can access this info directly, without opening the file, making it very fast:

Sub GetDataFromClosedFile()
filepath = "G:\fsoft\sos\data\ley"
FileName = "1cA10.sos" '<==this could change in a loop
sheetname = "General"
Strg = "sum('" & filepath & "\[" & FileName & "]" & sheetname &
"'!r11c23:r36c23)"
MsgBox ExecuteExcel4Macro(Strg)
End Sub

'In reality, it looked like this:
Sub GetDataFromClosedFile()
filepath = "G:\fsoft\sos\data\ley"
sheetname = "General"
For Each Fl In DialogSheets("DlgMulti").ListBoxes("MainList").List
Strg = "sum('" & filepath & "\[" & Fl & "]" & sheetname &
"'!r11c23:r36c23)"
Ans = ExecuteExcel4Macro(Strg)
If Ans > 0 Then Exit Sub
Next
'none > 0
'...rest of code goes here
End Sub