Link to home
Create AccountLog in
Avatar of bbroehm
bbroehm

asked on

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.  
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

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
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
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.
Avatar of bbroehm
bbroehm

ASKER

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  *********
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
ASKER CERTIFIED SOLUTION
Avatar of geneus
geneus
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account