• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7510
  • Last Modified:

Translating Excel 2003 Macros to Excel 2007

I currently have many Macro's written in Excel 2003 that I'm testing in Excel 2007.  Most of the macros add chart objects and begin by deleting all of the current charts using - ActiveSheet.ChartObjects.Delete,
2 questions 1.  What would the translation of the ActiveSheet.ChartObject.Delete command be and
2.  Is there any place / references that help in converting Excel 2003 Macro's to Excel 2007.  
0
bbroehm
Asked:
bbroehm
1 Solution
 
Rory ArchibaldCommented:
For the most part, as far as I know, the macros should be forward-compatible. The chart engine was completely revised for 2007 though so you may have some issues with those and you will find that the macro recorder does not work with charts in 2007! The only other thing I know of is that Application.FileSearch is discontinued.
I'm not in front of a machine with 2007 on at the moment to test, but are you getting errors with Chartobjects.Delete?
Regards,
Rory
0
 
Wayne Taylor (webtubbs)Commented:
HI bbroehm,

Have you tried that code in XL2007? As far as I know, it should still work.

Note that it should be ActiveSheet.ChartObjects.Delete, not ActiveSheet.ChartObject.Delete (note the plural ChartObject).

Regards,

Wayne
0
 
geneusCommented:
Question 1:

    ActiveSheet.ChartObjects("Chart 2").Delete
-OR-    
    ActiveSheet.ChartObjects(1).Delete

Question 2:
This will show you what the new changes in Excel 2007 are:
http://technet2.microsoft.com/Office/en-us/library/bee594b4-01b1-4d17-90ca-d43735a7382a1033.mspx?mfr=true

Most macros should work but there are object changes that you may need to tweak.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
bbroehmAuthor Commented:
A clarification I grabbed a snippet of code with the actual excel error *****'d about 10 lines down .  This code works fne in excel 2003 but not in excel 2007.  The excel error Run time error '1004'  Application defined or object defined eror.  Note the


    ActiveSheet.Cells(7, 1).Select
    'Sheets("System_Plots").Activate
   
    Sheets("System_Plots").Activate
    ActiveSheet.Range("a7").Select
    'ActiveSheet.Cells(7, 1).Select
    ActiveSheet.ChartObjects.Delete  *********
0
 
geneusCommented:
You have to identify the Chart Object to Delete.
If the ActiveSheet has only 1 chart on it then you can select by index:
ActiveSheet.ChartObjects(1).Delete

If you are deleting multiple charts on a single sheet:

    For a = 1 To ActiveSheet.ChartObjects.Count
        ActiveSheet.ChartObjects(1).Delete
    Next a
0
 
geneusCommented:
I tested your line:
ActiveSheet.ChartObjects.Delete

In both versions successfully.
The reason you may be getting the error is that the charts were created in a previous version of Excel than 2003 and they are not being recognized as charts.  The error messge you are getting means that the object, in your case, ChartObject, does not exist.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now