All Excel Properties Not Available When Updating Embedded Chart

Posted on 2009-04-27
Last Modified: 2013-12-18
I have a Lotus Notes form which has an embedded chart in it.  I am wanting to change the chart based on user input, but for some reason, some excel properties and/or methods do not seem available to me. For instance, in the following code, each line runs just fine EXCEPT the ".plotby".  This produces an OLE Autpmation Object Error.  

      Dim wb As Variant
      Dim ChartObject as Variant
      Dim xlSheet As Variant
      Dim myChart As Variant
      Dim sheet1 As Variant
      Set ChartObject= uDoc.GetObject("Chart1")
      Set wb = uDoc.GetObject("ExpenseSheet")
      Set sheet1 = wb.ActiveSheet

      Set myChart = ChartObject.Charts(1)
      With mychart      
            .HasTitle = True
            .ChartTitle.Text = "Monthly Expense Chart"
            .SetSourceData (sheet1.Range("A1:D9"))
            .PlotBy = xlcolumns
      End With

Yet, when I use virtually the same code in Excel, as shown below, I have no problem:

Dim chtObject As ChartObject
Dim mychart As Chart

    Set chtObject = Me.ChartObjects(1)
    Set mychart = chtObject.Chart
    With mychart
        .HasTitle = True
        .ChartTitle.Text = "Monthly Expense Chart"
        .SetSourceData Source:=sheet1.Range("A1:D9")
        .PlotBy = xlColumns
    End With

What am I missing here?  Is there a declaration I need to make somewhere?  

In a perhaps related issue, I am not able to use the " := " reference in LN the same way as I am in Excel, as shown in the 9th line of the above Excel example; it always produces an error.  Is this normal?  I'm really most concerned with my first issue, but since I'm here already, I thought I'd mention this since it may be related.

Thanks to all who respond.
Question by:sprice205
    LVL 46

    Expert Comment

    by:Sjef Bosman
    If xlColumns is a constant, you have to put that constant in...
    LVL 46

    Accepted Solution

    Try the value 2

    Author Comment


    sjef_bosman:  Using the value "2" worked! Thank you!  Now, I am, of course, absolutely going to award you the points, but could you  please indulge me a bit longer if you will and explain a bit about what you mean by "you must put those constants in,."  I'm afraid this is a great weak point in my development knowledge.  
    Could you please provide an example of how I would, for instance, put in the constant for "xlcolumns" ?  

    Thank you so much!

    LVL 46

    Expert Comment

    by:Sjef Bosman
    Excel works with predefined named constants, like xlColumns. Notes has no notion of these names, and if you do nothing special it creates a variable with the same name, with a value "" (empty string). Hence the error.

    Soo what you could do is
    - define a local or global variable xlColumns, and assign it the value 2
    - add a Const definition in LotusScript, much like the variable:
          Const xlColumns = 2
    - or just bluntly use a 2 instead of xlColumns, I suppose that's what you did

    Thanks for the grade!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now