Link between files with vba

[syaronc]

Hi all,
I have VB function that sums cells by the font color in my workbook an
I use this function on a linked file in this way:

=SumFontColor($A$1,'C:\LOGISTICS\A4\[NEW BOOKIN
SUMMARY.xls]N.BLDG'!E41)

For some reason all cells appear as "#value!" if the linked workbook i
closed
,if the linked workbook is opened all values appear as they should...


What am I doing wrong?

Please advise,
Thanks Yaron

p.s.

here is the above mentioned function:
Function SumFontColor(rFColor As Range, rFSumRange As Range)

Dim rFCell As Range
Dim iFCol As Integer
Dim vFResult

iFCol = rFColor.Font.ColorIndex

For Each rFCell In rFSumRange
If rFCell.Font.ColorIndex = iFCol Then
vFResult = WorksheetFunction.Sum(rFCell) + vFResult
End If
Next rFCell

SumFontColor = vFResult
End Functio

 

[Tom Ogilvy]

VBA doesn't support accessing a closed workbook.

 

[syaronc]

Do you have a creative idea how to bypass this problem ? in the secon
file i have money data that i don't want unothorized users to see...
Yaro

 

[Tom Ogilvy]

Possibly treat your workbook as a database and use ADO

http://www.erlandsendata.no/english/vba/adodao/index.php

However, I would suspect this would be slow if used in a worksheet function.

Perhaps you can do it using the old Excel 4 style macros.

 

[Rob]

I had the same problem using DSUM. Is the reason the same?

 

[syaronc]

Thanks for the help Tom,

can you please tell me what is "Excel 4 style macros" and where can
find them since i never heard about them,

Yaro

 

[syaronc]

any one with any good idea