Excel 2003 / Excel 2010 Compatibility Issue ??

[monirg]

Hello;

My earlier Thread in Excel Questions Forum:
VBA: Opening XL 2003 w/b in XL 2010 Produces Error ‘1004’ ??
http://www.mrexcel.com/forum/showthread.php?t=583852

I’d like to regard the problem as a compatibility issue rather than an excel question; particularly for those who might be interested in the subject or have encountered a similar problem.

Here’s in a nutshell a description of the problem and my earlier solution.

1) I need to transfer many of my XL 2003 workbooks (developed and run perfectly on different computers, XL 2003, Win XP 32-bit) to my new laptop (Win 7 Pro 64-bit, Office 2010 full version, 32-bit).

2) Any of those XL 2003 workbooks (with w/s password protection, macros, w/s events) produces in XL 2010 the error:
“Run-time error ‘1004”

3) In XL 2010, and been annoyed with the run-time error, I tried a w/b Open() event to unprotect the w/s in the transferred XL 2003 w/b, and a w/b BeforeClose() event to protect the w/s before exiting. Also tried calling a Sub to unprotect the w/s directly before doing anything. Nothing worked.
The Run-time error ‘1004’ persisted and appeared at different locations / different routines depending on the code changes.

4) Even unprotecting the w/s in the XL 2003 w/b before transferring the file to the new m/c running XL 2010 and then protecting the w/s in the XL 2010 environment before save/open didn’t work!
My earlier “inconvenient” solution (post # 6 in the above link)

to manually remove the w/s password protection (either before or after the transfer), and keep the w/s unprotected

remains the only solution (so far!) that’s working for me in XL 2010.

5) Since MS claims that a working XL 2003 w/b would run successfully AS IS in XL 2010 (i.e.; no VBA code modifications, no added events, no w/s attribute changes), I wonder if the problem should be looked at by MS Excel Development Team to possibly identify the XL 2010 bug and fix the problem once and for all.
(Will post here the MS solution, if any!)

Any comments ?? Is communicating with MS Excel Team worth the time and effort ??

Regards.

 

[Derek Brown]

Two thoughts:
1. Are you 'converting' the workbooks from 2003 format to 2007/2010 format? (File | Info | Convert)
2. Are you opening the files from a Trusted Location? (File | Options | Trust Center .....)

 

[monirg]

Hi Derek;

Converting and Macro Settings made no difference!

1) When I open the XL 2003 file in XL 2010, and under:
Developer::Macro Security –> Trust Centre::Macro Settings
I have the following two boxes checked:
O Disable all macros with notification
O Trust access to the VBA project object model

2) And under:
Trust Centre::Message Bar
I have the top box checked:
O Show the Message Bar in all ...
and the bottom box unchecked: (I don’t know what it does!)
O Enable Trust Centre logging

3) Do you have the same settings on your system ??

4) I’ve tried different combinations for Macro Settings with no luck.

5) Incidentally, I’ve never seen the Message Bar displayed even once so far!
I thought it would be a good idea to have the message “Enable macros” “Disable macros” displayed when you open an excel file in XL 2010 (like in the good old days: XL 2003, Macro Security set to Medium).
Even with the above option "Trust access to the VBA project object model" unchecked.

6) MS Excel Technical Support Team Manager has just replied:

... will forward this information to the concerned team at MS (to investigate) and will let you know.

Regards.

 

[xenou]

When you protect worksheets with these charts on them, have you specified that you will allow users to select charts?

ActiveSheet.Protect DrawingObjects:=False

 

[monirg]

Hi xenou;

1) Hope I understood and tried your suggestion correctly:
-> opened the w/s_password_protected XL 2003 w/b in XL 2010.
-> in the macro code, I inserted the statement:

ActiveSheet.Protect DrawingObjects:=False

before:

ActiveSheet.ChartObjects("Chart 1").Activate

-> ran the macro
-> a MsgBox “Unprotect Sheet” appeared asking for the Password.
-> entered the password and pressed OK
-> the w/s protection was removed and everything worked fine
Same as manually unprotecting the w/s (my earlier suggestion)

2) Still in XL 2010, Cancel the “Unprotect Sheet” MsgBox (i.e.; keep the w/s protected), run the macro, and the Run-time error ‘1004’ re-appears with the following statement highlighted:

ActiveSheet.ChartObjects("Chart 1").Activate

Same as reported earlier.

Regards.

 

[xenou]

Same as manually unprotecting the w/s (my earlier suggestion)

2) Still in XL 2010, Cancel the “Unprotect Sheet” MsgBox (i.e.; keep the w/s protected), run the macro, and the Run-time error ‘1004’ re-appears with the following statement highlighted:
Code:

No.

You want to protect the sheet with drawing objects allowed to be edited. I.e., you cannot activate a chart on a protected sheet (maybe you could before but now you can't). Another possibility would be to try UserInterfaceOnly = True, though I've had mixed results with that and I don't consider it to be reliable).

The point is to set the protection more precisely. Not just "protect" but protect with the exact options you want.

Edit:
You can also, btw, just unprotect the sheet, do your code thing, and re-protect it. This is pretty much standard procedure in most cases where you need to do work on a protected sheet (unless you are employing the UserInterfaceOnly option, which I don't quite trust).

 

[xenou]

I'm not a chart wiz with VBA but maybe it is not necessary to activate the chart. Though my guess is that you would still need it to be in an editable state as far as protection options are concerned (not sure - haven't tried it).

 

[monirg]

Hi xenou;

1) I meant to say: “Same results” in the quote you included. Sorry!
2) I did try earlier UserInterfaceOnly = True; and it didn’t solve the problem!
3) As far as your suggestion:

just unprotect the sheet, do your code thing, and re-protect it

which was the logical thing to try:

Monir wrote in the OP:
3) In XL 2010, and been annoyed with the run-time error, I tried a w/b Open() event to unprotect the w/s in the transferred XL 2003 w/b, and a w/b BeforeClose() event to protect the w/s before exiting. Also tried calling a Sub to unprotect the w/s directly before doing anything. Nothing worked.
The Run-time error ‘1004’ persisted and appeared at different locations / different routines depending on the code changes.

4) I suspect the message:

Run-time error ‘1004’
Application-defined or object-defined error

with reference to the code statement:
ActiveSheet.ChartObjects("Chart 1").Activate
may not necessarily (at least in this case) be a true VBA-Library-Generated Notification to exactly where the the problem is detected by XL 2010 (and not by XL 2003).
Just a thought!
5) You questioned:

maybe it is not necessary to activate the chart.

I’ve looked at a number of charts (incl. the chart in question), and it is necessary to activate the chart in the relevant VBA code.

Regards.

 

[xenou]

I can't find a .minimumscale attribute for category axis in XL2010. Have you noticed that?

I also don't, by the way, think that 2003 code is supposed to be 100% executable in 2010. The chart object model, in particular, underwent significant changes. Can you support your claim about Microsoft's claim ("a working XL 2003 w/b would run successfully AS IS in XL 2010 (i.e.; no VBA code modifications, no added events, no w/s attribute changes")?

 

[monirg]

Hi xenou;
Thank you for your thoughtful reply.
1) You wrote:

I can't find a .minimumscale attribute for category axis in XL 2010. Have you noticed that?

Very interesting! I haven’t noticed that, but will look at it after the game!

2) You wrote:

I also don't, by the way, think that 2003 code is supposed to be 100% executable in 2010. The chart object model, in particular, underwent significant changes.

That’s true, but don’t think it could be blamed for the error, and here’s why

3) Realizing one needs only a very minor deviation from the VBA code conformity to get an error, one might reasonably argue that neither 1) or 2) above is/could be the culprit, since by manually removing the w/s password protection from the XL 2003 w/b and doing nothing else, the w/b works fine and as desired in XL 2010.

Regards.