Excel Crashing using Remote Worksheet Calls in NETWORKDAYS

[Paul Doman]

Hi all...

We're having problems attempting with Excel 2007 crashing when referencing a
lookup table in Worksheet 'B' from Worksheet 'A' during a NETWORKDAYS
call.... In more detail...

Worksheet 'A' has a list of rows including two date columns. We're
attempting to workout the difference in working days between the two date
columns taking into account a list of "holiday" dates contained within
Worksheet 'B'. We are using NETWORKDAYS function for this.

Everything works fine whilst Worksheet 'B' is open, however when you close
Worksheet 'B' (or open Worksheet 'A' from a freshly opened Excel) then Excel
will crash & burn (restart of Excel required).

Some things I've tried...

Worksheet A & B both live on Network shares with the same access rights to
the logged on client who's using Excel.

Replacing NETWORKDAYS with any other innocuous call (ie VLOOKUP) does not
crash Excel, even though it's referencing the lookup table in Worksheet 'B'
which is NOT open.

I've used the Trust Centre to fully trust all I can (as an experiment)
however this seems to have no affect and still causes the Excel Crash.

My thoughts...

My understanding is that in previous versions of Excel the NETWORKDAYS
function was included in an Analysis Toolpack - implying that it was either
a VB Macro or Excel Add-in based function. I'm assuming there's some kind
of problem interpreting the path to the Excel file for (in our case
Worksheet 'B') Or there may be a rights issue (although I've almost
discounted that by locating Worksheet 'B' in various network / local
locations).

Any help would be appreciated folks!

Paul

 

[Jim Rech]

I duplicated your problem or one very similar to it. Since the two files I
created were on my local machine there are no rights or network issues
involved. I'd say you have uncovered a bug and it would seem you'll have to
find a way to get the holidays into the same workbook as the formula.

--
Jim
| Hi all...
|
| We're having problems attempting with Excel 2007 crashing when referencing
a
| lookup table in Worksheet 'B' from Worksheet 'A' during a NETWORKDAYS
| call.... In more detail...
|
| Worksheet 'A' has a list of rows including two date columns. We're
| attempting to workout the difference in working days between the two date
| columns taking into account a list of "holiday" dates contained within
| Worksheet 'B'. We are using NETWORKDAYS function for this.
|
| Everything works fine whilst Worksheet 'B' is open, however when you close
| Worksheet 'B' (or open Worksheet 'A' from a freshly opened Excel) then
Excel
| will crash & burn (restart of Excel required).
|
| Some things I've tried...
|
| Worksheet A & B both live on Network shares with the same access rights to
| the logged on client who's using Excel.
|
| Replacing NETWORKDAYS with any other innocuous call (ie VLOOKUP) does not
| crash Excel, even though it's referencing the lookup table in Worksheet
'B'
| which is NOT open.
|
| I've used the Trust Centre to fully trust all I can (as an experiment)
| however this seems to have no affect and still causes the Excel Crash.
|
| My thoughts...
|
| My understanding is that in previous versions of Excel the NETWORKDAYS
| function was included in an Analysis Toolpack - implying that it was
either
| a VB Macro or Excel Add-in based function. I'm assuming there's some
kind
| of problem interpreting the path to the Excel file for (in our case
| Worksheet 'B') Or there may be a rights issue (although I've almost
| discounted that by locating Worksheet 'B' in various network / local
| locations).
|
| Any help would be appreciated folks!
|
| Paul
|

 

[Paul Doman]

Thanks Jim - that's what I thought - ah well, I'll try attacking it with a
Custom VB Function and see how that goes - I'll keep you posted.

 

[Alan124]

Hi all...

We're having problems attempting withExcel2007crashing when referencing a
lookup table in Worksheet 'B' from Worksheet 'A' during a NETWORKDAYS
call....  In more detail...

Worksheet 'A' has a list of rows including two date columns.  We're
attempting to workout the difference in working days between the two date
columns taking into account a list of "holiday" dates contained within
Worksheet 'B'.   We are using NETWORKDAYS function for this.

Everything works fine whilst Worksheet 'B' is open, however when you close
Worksheet 'B' (or open Worksheet 'A' from a freshly openedExcel) thenExcel
willcrash& burn (restart ofExcelrequired).

Some things I've tried...

Worksheet A & B both live on Network shares with the same access rights to
the logged on client who's usingExcel.

Replacing NETWORKDAYS with any other innocuous call (ie VLOOKUP) does notcrashExcel, even though it's referencing the lookup table in Worksheet 'B'
which is NOT open.

I've used the Trust Centre to fully trust all I can (as an experiment)
however this seems to have no affect and still causes theExcelCrash.

My thoughts...

My understanding is that in previous versions ofExcelthe NETWORKDAYS
function was included in an Analysis Toolpack - implying that it was either
a VB Macro orExcelAdd-in based function.   I'm assuming there's some kind
of problem interpreting the path to theExcelfile for (in our case
Worksheet 'B') Or there may be a rights issue (although I've almost
discounted that by locating Worksheet 'B' in various network / local
locations).

Any help would be appreciated folks!

Paul

Hi,

Maybe the excel files are damaged or something, and if they really do,
I think you can try a utility called Advanced Excel Repair to repair
your Excel xls file. It works rather well for my corrupt Excel xls
files. Its web address is http://www.datanumen.com/aer/

Hope this will help.

Alan