Workbook updated from Access

[Giangi]

Hello everybody.
I have to create from access 97 a workbook with advanced adds with a typical
code:
Dim XL As Excel.Application, WKB As Workbook, SHT As Worksheet
Set XL = New Excel.Application
With XL
.Visible = True
Set WKB = .Workbooks.Add(xlWBATWorksheet)
End With
.....
CopyFromRecordset RS
......

and I would calculate work days from two dates with function:
Cells(4,17).Select
Cells(4, 17).FormulaR1C1 = "=NETWORKDAYS(R[1]C[-8],RC[-7],Holidays)"

but Excel return error #NAME in the cell, also if options "advanced adds"
are selected in the workbook.
Therefore, I have deselected these options, closed the window, re-open
window for re-selecting advanced options, then, after selected cell, I pushed
function button (fx) and confirmed: function runned exactly!
I am very confused about this fenomena: someone help me?
Is another way to create a workbook ?
Many thanks in advance.

 

[OssieMac]

You do not have anything in your code to show where you have named a range in
Excel that contains the named range "Holidays". Neither can I see where you
have exported data to Excel for the holidays.

The formula must either have a valid Excel range for the list of holidays or
the range containing the list of holidays must be named in the Excel
worksheet. Recording some code to name a range on an Excel worksheet will
give you the syntax to use in VBA to name the range.

Of course if there are no holidays then the holidays part of the formula is
optional and can be left out.