none
Excel 2007 error with Chart.SeriesCollection.NewSeries RRS feed

  • Question

  • Please have a look at this code snippet.When I run this on a machine with Excel 2007 I get an error on the first SeriesCollection.NewSeries line.The same script works fine in Excel 2010.

    The error on the line is :

    Error: Automation object member not found

    Any reason Excel version would cause this conflict?Your help will be much appreciated.

     

    Set xlApp = CreateObject("Excel.Application") 
    
    xlApp.Visible =True 
    
    xlApp.displayAlerts=True 
    
    xlApp.Workbooks.Add 
    
     
    
    Set xlsheet = xlapp.workbooks(1).worksheets.add(Null, xlsheet) 
    
    xlSheet.Name="Field Activity" 
    
     
    
    Set ActiveChart=xlApp.Workbooks(1).charts.add 
    
    ActiveChart.activate 
    
    ActiveChart.ChartType = 65 
    
    ActiveChart.SeriesCollection.NewSeries 
    
    ActiveChart.SeriesCollection.NewSeries 
    
    
    
    


    Tuesday, August 23, 2011 1:21 PM

All replies

  • This is odd, I take it you haven't pasted all your actual code
     Set xlsheet = xlapp.workbooks(1).worksheets.add(Null, xlsheet)

    cht.SeriesCollection.NewSeries

    NewSeries as above should work fine in Excel 2007 as it has in all versions since 97,assuming cht refers to a an Excel chart. Try changing your variable name "ActiveChart" to something that is not a keyword, eg cht. Also declare your variables, eg

    Dim cht as Excel.Chart ' if early binding
    Dim cht as Object ' late binding

    Which app does your code exist in.

    Peter Thornton

    Tuesday, August 23, 2011 2:15 PM
    Moderator
  • This is odd, I take it you haven't pasted all your actual code
     Set xlsheet = xlapp.workbooks(1).worksheets.add(Null, xlsheet)

    cht.SeriesCollection.NewSeries

    NewSeries as above should work fine in Excel 2007 as it has in all versions since 97,assuming cht refers to a an Excel chart. Try changing your variable name "ActiveChart" to something that is not a keyword, eg cht. Also declare your variables, eg

    Dim cht as Excel.Chart ' if early binding
    Dim cht as Object ' late binding

    Which app does your code exist in.

    Peter Thornton


    Thanks for your response Peter.Yes,there is more code that I have not posted.I renamed the variable to cht like you indicated and same result.I keep getting the error message " Automation object member not found " on the line NewSeries.I am using this code in a Lotus Notes application and it does not allow me to declare Excel charts or objects.

    I would appreciate your assistance.

    Thanks.

    Wednesday, August 24, 2011 12:05 PM
  • I renamed the variable to cht like you indicated and same result.I keep getting the error message " Automation object member not found " on the line NewSeries.I am using this code in a Lotus Notes application and it does not allow me to declare Excel charts or objects.

    Afraid I can't replicate in Lotus Notes, I didn't even know it has VBA, or maybe it's just some form of script?

    Suggest first test your code in Word-VBA using Late binding. That's to say declare your object variables 'As Object' but don't use any keywords for variable names like ActiveChart. Also for testing maybe don't bother with adding a new worksheet and go straight on the adding the chart-sheet.

    Try again but don't declare any variables at all.

    If that works try the same as a VBS script. Don't declare any variables, place the code (without Sub stub) in a text file with the extension *.vbs. Maybe save it to the desktop and double click it.

    If things are still going wrong, in any of the above or in your Lotus, try fully qualifying rather than using object variables

    xlApp.ActiveChart.SeriesCollection.NewSeries

    note in the above chart 'ActiveChart' is not a variable

    if that works try

    Set sr = xlApp.ActiveChart.SeriesCollection.NewSeries
    and work with sr for adding data etc
    or if that fails (but you've got the new series)
    xlApp.ActiveChart.SeriesCollection(1).etc

    Peter Thornton

    Wednesday, August 24, 2011 12:47 PM
    Moderator
  • I am trying these ideas as you suggested and I will let you know what I find.I wanted to let you know that this script(causing the error) is running on a machine that runs Windows 2003 with Excel 2007.

     

    Thursday, August 25, 2011 1:08 PM
  • Hey guys,

    Just want to ask if anyone got this fixed? I got the same error in my lotusscript code. If you can share your findings, appreciate it.

    Thanks

     

    Landz

    Thursday, November 24, 2011 2:50 AM