VBA: Opening XL 2003 w/b in XL 2010 Produces Error ‘1004’ ??

[monirg]

Hello;

1) I have no choice but to transfer 10s of my (working perfectly) XL 2003 files to a new laptop running XL 2010 (full version).

2) Opening the 1st XL 2003 file in XL 2010 produces the error:

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

with the first ActiveSheet statement in the following abbreviated macro code (in a standard Module) highlighted:

'change chart XR scale depending on TIP or HUB region
[B]ActiveSheet.ChartObjects("Chart 1").Activate[/B]
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
    If Range("J41") = "TIP" Then
       .MinimumScale = 0.7
       .MaximumScale = 1.05
    Else
       .MinimumScale = 0.15
       .MaximumScale = 0.71
    End If
End With

3) I’ve tried few changes to the ActiveSheet statements, e.g.; Application.ActiveSheet, Workbook.ActiveSheet, etc., with no luck!
I’ve also checked the VBA Editor::Tools::References
and found the checked references in XL 2010 to be identical to those checked in XL 2003 (obviously with Office 14.0 instead of Office 11.0)

4) It’s likely that numerous similar errors will be encountered, and I wonder if there’s a feature or a reference in XL 2010 I should check first before considering changes to the XL 2003 VBA code throughout.

Your expert advice would be greatly appreciated.

 

[Derek Brown]

Have you checked to see if Excel 2010 has changed the name of the Chart?
There was a big change to charting at Excel 2007 (and consequently 2010 also):
http://peltiertech.com/WordPress/changes-to-charting-in-excel-2007/
You could try finding the chart using its 'number' - have a look at an example of this method at:
http://www.teachexcel.com/excel-help/excel-how-to.php?i=168867

 

[monirg]

Hi Derek;

Thank you for your reply.

1) The 1st thing I did was to check the chart object “Chart 1”. It was correct.

2) I also copied another Excel file (developed on Win XP, XL 2003, with a macro, NO charts) to the new laptop (Win 7 Pro, 64-bit, XL 2010 Full version).
Everything worked fine.

3) Having the XL 2003 workbooks (with charts) working perfectly in XL 2003 and failing in XL 2010 may suggest a problem with the Office 2010 installation or a corrupt XL 2010 component.

4) If compatibility of charting is a tricky issue in later XL versions, then I think MS should provide the users with an exact or a workaround solution to the problem.

5) I’ve just emailed my detailed question to MS Excel 2010 Support, and expect a reply within 1 business day indicating:
a. it is an installation problem, and here’s what you should do .....
b. it is NOT a damaged XL 2010, and here’s what you should do .....
c. don’t know, and the issue has been forwarded to the MS Excel Development Group

Regards.

 

[Derek Brown]

Whenever I see something like "Win 7 Pro, 64-bit, XL 2010 ....." I have to wonder if it is again because the 64-bit version of Office has been installed on Windows 64-bit"?
I have come across some obscure problems as a result of that configuration.

Microsoft recommended that 32-bit Office 2010 be installed on Windows 64-bit:
http://office.microsoft.com/en-us/w...-version-of-microsoft-office-HA010369476.aspx


This may not be the cause of your problem but I don't encounter a problem when I run your code on my PC (Windows 7 64-bit/Office 2010 32-bit).

 

[monirg]

Hi Derek;

Last week I installed both Win 7 64-bit and Office 2010 (full version) on my new laptop.
I don’t specifically recall been prompted to select 32-bit or 64-bit during the Office 2010 installation!
It is my impression, however, that the 32-bit is the default version, but I could be wrong!

It is encouraging that you were able to duplicate the scenario on your system
(Windows 7 64-bit/Office 2010 32-bit) and encountered no problem!

How do I check which version (32-bit or 64-bit) of Office 2010 I’m running ??
And does it by default apply to ALL Office components (unless one chooses differently for each Office program) ??

Regards.

 

[monirg]

Hello;

Here is the solution to my question (OP) which I’ve just arrived at by pure accident!

Remove the Worksheet Protection from the XL 2003 workbook and it would open fine and run smoothly in XL 2010.

Now I’d have to do that for ALL XL 2003 files (before or after) been transferred to the new laptop running XL 2010.
It is very inconvenient, but at least resolves the issue.


Regards.

 

[monirg]

Hello;

For a better solution discussion is continued at:

Excel 2003 / Excel 2010 Compatibility Issue ??
http://www.mrexcel.com/forum/showthread.php?p=2898282#post2898282

Regards.