Hi All,

I have developed a workbook in XL2003 which is 24.27MB in size which
is heavily populated with formulas but nothing particularly complex.
If, Iserror, Sumif being the mainstay.

There is a small amount of VBA code triggered by the Workbook_Open
event (posted below)

The problem is this:

When l open the workbook in XL2007 everything appears fine, the
workbook calculates (showing that 2 processors are being used) then
when the calculation has ended l get a message stating that XL has
encountered a problem with the workbook and is trying to recover the
information!

I have tried various ways of opening / saving / transferring this file
into the XL2007 environment but without success. Only on one occasion
did the recovery message successfully complete stating that it had
found 'invalid conditional formatting' but without any details. I am
not convinced this is the cause, surely any conditional format
statement that works with XL2003 will work with XL2007, and
additionally how am l meant to locate the offending cells(s) in a
workbook of this size?

The original file is not corrupted as l have successfully opened it on
another machine running XL2003

Is there a site anywhere that lists all known XL2007 issues /
problems?

Can anybody shed any light on this problem?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call MsgBox("This workbook has been set to manual calculation to
enhance its operation." _
& vbCrLf & "" _
& vbCrLf & "Pressing F9 to calculate the workbook will
ensure that the latest results are displayed." _
, vbInformation, "Information")

Filename = ActiveWorkbook.Name
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.DisplayAutomaticPageBreaks = False
Sht1.EnableAutoFilter = True
If Sht1.Name Like "*Volumes" Or Sht1.Name Like "*Outputs" Then
Sht1.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1
Else
Sht1.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End If
Sht1.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sht1.Range("A1").Select
Next Sht1
Sheets("Start Here").Select

End Sub

Regards

Michael Beckinsale

Michael,
If you posted your entire code...
1. You did not declare the Filename and Sht1 variables.
2. It is not clear as to whether "ActiveWorkbook" is the workbook that contains
the code. If it is, I would replace ActiveWorkbook with "ThisWorkbook".
3. Filename is a property of the Assistant object and the FileSearch object
and should not be used as a variable name.
Furthermore, the FileSearch object was removed from XL2007 and
who knows what Excel 2007 thinks it has found when it sees that variable.

Of course, fixing all of the above may not cure your problem. <g
(some people refer to XL2007 (12) as the beta for version 14)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"michael.beckinsale"
wrote in message
Hi All,
I have developed a workbook in XL2003 which is 24.27MB in size which
is heavily populated with formulas but nothing particularly complex.
If, Iserror, Sumif being the mainstay.

There is a small amount of VBA code triggered by the Workbook_Open
event (posted below)

The problem is this:

When l open the workbook in XL2007 everything appears fine, the
workbook calculates (showing that 2 processors are being used) then
when the calculation has ended l get a message stating that XL has
encountered a problem with the workbook and is trying to recover the
information!

I have tried various ways of opening / saving / transferring this file
into the XL2007 environment but without success. Only on one occasion
did the recovery message successfully complete stating that it had
found 'invalid conditional formatting' but without any details. I am
not convinced this is the cause, surely any conditional format
statement that works with XL2003 will work with XL2007, and
additionally how am l meant to locate the offending cells(s) in a
workbook of this size?

The original file is not corrupted as l have successfully opened it on
another machine running XL2003

Is there a site anywhere that lists all known XL2007 issues /
problems?

Can anybody shed any light on this problem?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call MsgBox("This workbook has been set to manual calculation to
enhance its operation." _
& vbCrLf & "" _
& vbCrLf & "Pressing F9 to calculate the workbook will
ensure that the latest results are displayed." _
, vbInformation, "Information")

Filename = ActiveWorkbook.Name
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.DisplayAutomaticPageBreaks = False
Sht1.EnableAutoFilter = True
If Sht1.Name Like "*Volumes" Or Sht1.Name Like "*Outputs" Then
Sht1.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1
Else
Sht1.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End If
Sht1.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sht1.Range("A1").Select
Next Sht1
Sheets("Start Here").Select

End Sub

Regards

Michael Beckinsale

Hi Jim,

Thanks for responding.

I have 'dimmed' the filename - copy / paste error.

This is basic VBA code that l have used for many years to loop thru
sheets in workbook and l have never declared Sht1 as a variable, and
have always used filename.

However taking on board your comments l have changed ActiveWorkbook to
ThisWorkbook and changed 'filename' to 'MyWB'

Result - No change! it still crashes.

It will be VERY disappointing if basic VBA code like this needs to be
amended to run in XL2007 (SP1)

After further tests I now believe that it is the VBA code that is
causing the problem because when l strip out the Workbook_Open event l
can open the workbook fine & save the file as XL2007.xlsx

All l can do now is try to isolate the the lines causing the
problem(s) and amend as necessary.

Regards

Michael

Michael -

Open the workbook in 2003, enter 'Stop' as the first line within the sub,
and save the workbook. Reopen in 2007, the code will be interrupted at
'Stop', then step through using the F8 key. This may help you find the
offending code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"michael.beckinsale" wrote in message
...
Hi Jim,

Thanks for responding.

I have 'dimmed' the filename - copy / paste error.

This is basic VBA code that l have used for many years to loop thru
sheets in workbook and l have never declared Sht1 as a variable, and
have always used filename.

However taking on board your comments l have changed ActiveWorkbook to
ThisWorkbook and changed 'filename' to 'MyWB'

Result - No change! it still crashes.

It will be VERY disappointing if basic VBA code like this needs to be
amended to run in XL2007 (SP1)

After further tests I now believe that it is the VBA code that is
causing the problem because when l strip out the Workbook_Open event l
can open the workbook fine & save the file as XL2007.xlsx

All l can do now is try to isolate the the lines causing the
problem(s) and amend as necessary.

Regards

Michael

Jim & Jon,

Thanks for your input so far.

Now l really am confused.

1) When l take out the 'If' statement containing the 'Like' functions
the workbook saves & calculates properly. I have closed, opened &
calculated the file several times without error.

2) When l leave in the 'If' statement using an 'On Error Resume Next'
the workbook will save. When l re-open it the Workbook_open event runs
correctly (l put a msgbox in to tell me it was at the end of the
code). However when l press F9 to calculate l still get the dreaded
'Excel has encountered a problem' message.

I have run the full diagnostics facility within Office 2007 and no
errors were reported.

I am wondering if it has anything to do with the calculation options
set in Excel.

Any idea's

Regards

Michael.

Michael,
1. Did you ever declare the Sht1 variable?
Dim Sht1 as Excel.Worksheet

2. Enter "Option Explicit" as the first line of your code!

3. Change "DisplayAutomaticPageBreaks" to DisplayPageBreaks
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"michael.beckinsale"

wrote in message
Jim & Jon,
Thanks for your input so far.
Now l really am confused.

1) When l take out the 'If' statement containing the 'Like' functions
the workbook saves & calculates properly. I have closed, opened &
calculated the file several times without error.

2) When l leave in the 'If' statement using an 'On Error Resume Next'
the workbook will save. When l re-open it the Workbook_open event runs
correctly (l put a msgbox in to tell me it was at the end of the
code). However when l press F9 to calculate l still get the dreaded
'Excel has encountered a problem' message.

I have run the full diagnostics facility within Office 2007 and no
errors were reported.

I am wondering if it has anything to do with the calculation options
set in Excel.

Any idea's

Regards

Michael.

Hi All,

I have managed to overcome the problem but l'm not sure if it is
coincidence or not. You know how it is when you try numerous things
and suddenly things work as they should.

I had the 'Addintools - Classic Menu for Excel' COM add in installed.
When l de-activated the add-in l could save & calculate the file as
required. I then re-activated the add-in and the workbook behaved
correctly.

When l get time l will try to replicate the problem and isolate to the
add-in being the cause.

Regards

Michael