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

--
Message posted from http://www.ExcelForum.com

VBA doesn't support accessing a closed workbook.

--
Regards,
Tom Ogilvy

"syaronc " wrote in message
...
Hi all,
I have VB function that sums cells by the font color in my workbook and
I use this function on a linked file in this way:

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

For some reason all cells appear as "#value!" if the linked workbook is
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 Function


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Possibly treat your workbook as a database and use ADO

http://www.erlandsendata.no/english/...odao/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.

--
Regards,
Tom Ogilvy

"syaronc " wrote in message
...
Do you have a creative idea how to bypass this problem ? in the second
file i have money data that i don't want unothorized users to see...
Yaron


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

any one with any good idea

--
Message posted from http://www.ExcelForum.com