Excel fails to open workbook depending on Location?!?!?!

[RiTz21]

I got this workbook located in "C:\TEXT\Character Sheet" which opens
fine after double-clicking on it and enabling Macros.

If I move this workbook to "C:\test" and try to open it by
double-clicking, I get the dreaded "Excel has encountered a problem and
needs to close" worthless error after enabling the Macros.

If I open Excel first, then use File->Open to open the workbook in
"C:\test", there are no problems.

If I move the workbook to the "C:\test\excel sheet" folder and
double-click on the workbook, it opens FINE without any errors after
enabling the Macros.

Could someone explain WHY this happends?!
Is there something that can be done so the Workbook does NOT crash
because of it's location or because it's been doubled-clicked on ?!

Ssince this workbook is used by a bunch of people, asking them to place
it in a particular folder is NOT a practical option - Excel should not
crash, where ever the workbook is located....

Thanks!

RT

 

[Jan Karel Pieterse]

Hi RiTz21,

I got this workbook located in "C:\TEXT\Character Sheet" which opens
fine after double-clicking on it and enabling Macros.

If I move this workbook to "C:\test" and try to open it by
double-clicking, I get the dreaded "Excel has encountered a problem and
needs to close" worthless error after enabling the Macros.

Could you post the code that is in the workbook (the part that fires when
the workbook is opened)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[RiTz21]

Mr Pieterse,

The error "Excel has encountered a problem and needs to close" happens
before the workbook is completely loaded (at around 10% of its loading
process!)

Here it is anyways (in case...)
Private Sub Workbook_Open()
i_tSel = 0
For i = 1 To Names.Count
If Names(i).Name Like "tSel_*" Then
i_tSel = i_tSel + 1
End If
Next

' Init
bEnableControlActivate = True

#If Mac Then
ActiveWorkbook.Names("IsMac").RefersToRange = True
#Else
ActiveWorkbook.Names("IsMac").RefersToRange = False
#End If

End Sub

More info: on my PC, if I "repair" the workbook (with my Excel 2003),
Excel repairs it and removes some conditional formatting. It gives this
in the Repair log:
"One or more invalid conditional formats were removed from the
workbook"


Thanks for any help you can provide on this most annoying problem!

RiTz21

 

[Jan Karel Pieterse]

Hi RiTz21,

The error "Excel has encountered a problem and needs to close" happens
before the workbook is completely loaded (at around 10% of its loading
process!)

Maybe the code contains debris that needs removing. Have you tried
cleaning the VBA code? (use Rob Bovey's code cleaner: www.appspro.com)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[RiTz21]

Alas, this problem persists even if I remove ALL the VBA code in the
Workbook...

I tried again this morning:

In "C:\test\excel sheet\" the workbook opens fine (by double-click)
if I MOVE it to "C:\test\" and double-click on it, it FAILS after
enabling Macros...

This is a _move_ I did - it is the same workbook!! Is there something
preventing Excel from opening a Workbook in ONE folder, yet it can in
Another folder?!?! Would there be an interaction between excel and an
external application that would cause this?

I do NOT understand what's wrong here... and this is absurdly stupid.

HELP!
R

 

[RiTz21]

Addendum: If I use DRAG & DROP and drop the workbook in Excel 2003
(meaning Excel 2003 is already open) it works, irrelevant of the
location)

Yet double-clicking fails as mentionned above...

Why has Microsoft chosen to give absolutely NO indication of Excel
Failures?!
"Microsoft Office Excel has encountered a problem and needs to close.
We are sorry for the inconvenience." is TOTALLY useless... Is it still
possible, in our day and age, that an advanced piece of software would
be 'sorry' !?!? At least TELL me what's wrong, and I'll fix it...

My apologies, I'm a bit frustrated...

R

 

[Jan Karel Pieterse]

Hi RiTz21,

This is a _move_ I did - it is the same workbook!! Is there something
preventing Excel from opening a Workbook in ONE folder, yet it can in
Another folder?!?! Would there be an interaction between excel and an
external application that would cause this?

Can you mail me the file?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[RWN]

Quoting from Dave Peterson;
<quote>
"Sometimes one of these works:

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver"
<endquote>

I've found that when I have problems along the lines of yours that the re-registration of
Excel typically fixes them.
At any rate they're quick/easy to try.

HTH

 

[RiTz21]

I tried both suggestions, but it does not work.

Weirdly enough, the workbook opens fine on Excel 2000... Yet excel 2002
can't open it.
Why would a workbook open fine on excel 2000 (and in the excel viewer)
and yet fails to open in excel 2002 ??? (Macros have been removed, and
still no luck)

HELP!
R

 

[RiTz21]

I found the problem and though I should post it so the MVPs can add
this tidbit to their pool of knowledge...

For some reason, Excel 2002 crashed because of the very high number of
different cell formats... Something Excel 2002 had no issue with.

When I reduced that number down (by selecting a whole worksheet and
deleting all the conditionnal formats within (which I assume are part
of that 4,000 maximum)), it worked on Excel 2002 without any more
problems...

Go figure...

Now, is there a tool/way to measure the number of different cell
formats in a Workbook?? I'd LIKE to keep monitoring this value as it's
critical to my Workbook... (hell a VBA method that I could link up to a
Button would be great !!)

THANKS to all who gave Help!

RiTz21

 

[RiTz21]

woups - correction:
"Something Excel 2000 had no issue with"

R