Problem using VBA for the Networkdays function

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

Hi James,

Range("D6").FormulaR1C1 = "=networkdays(rc[-3],rc[-2])"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"jamesp@premiercs.co.uk" <jamesppremiercscouk@discussions.microsoft.com> wrote in message
news:5D68B2EC-A845-4344-85F5-701A6963E6D8@microsoft.com...
|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

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

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

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
>
>
>

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
> >
> >
> >

Hi James,

And after you've sorted that out, check the cell references in your formula in the worksheet; it IS A6 and B6 (Columns -3 and -2,
not Rows)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message news:uQSq4n7vGHA.4444@TK2MSFTNGP05.phx.gbl...
| 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
| > >
| > >
| > >
|
|