James,

When you use Excel via automation, the addins do not get loaded, so you get
the error.

Force the load like so


xlApp.AddIns("Analysis ToolPak").Installed = False
xlApp.AddIns("Analysis ToolPak").Installed = True
xlApp.Range("D6").Value = "=networkdays(rc[-3],rc[-2])"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jamesp@premiercs.co.uk" <jamesppremiercscouk@discussions.microsoft.com>
wrote in message news:4C8ECC36-2BEF-48A2-8509-142531756B69@microsoft.com...
> it is D4 and D5. More importantly the function name is not in capitals
> implying that XL can't see the function name. The Analysis toolpak has
been
> ticked in the Add-ins
>
> Try it creating a simple macro in Word - I can give you the code if you
want
>
>
> "Niek Otten" wrote:
>
> > Be aware that your formula is not referring to D4 and D5, but to A6 and
B6. Are any of them #NAME?
> > I assume that you have Analysis Toolpak ticked in your worksheet Add-ins
menu, not (just) VBA
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> > Microsoft MVP - Excel
> >
> > "jamesp@premiercs.co.uk" <jamesppremiercscouk@discussions.microsoft.com>
wrote in message
> > news:01147221-32FF-47E3-B2D5-6F30A2B131FD@microsoft.com...
> > | Sorry Niek doesn't work. In my experience .value and .formular1c1
work the
> > | same
> > |
> > | James
> > |
> > | "jamesp@premiercs.co.uk" wrote:
> > |
> > | > I am using VBA in MS project to enter information into Excel 2003.
It
> > | > creates an instance and enters various dates. However if I put the
> > | > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
> > | > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I
get the
> > | > #NAME? error message. The Add-ins for the Analysis toolpak have
been ticked.
> > | >
> > | >
> > | > I tried running code to turn the analysis toolpak off and then on
> > | > programatically but still the same error message
> > | >
> > | > Any ideas how this can be fixed?
> > | >
> > | > Many thanks
> > | >
> > | > James
> >
> >
> >