All Excel Properties Not Available When Updating Embedded Chart

Posted on 2009-04-27
Medium Priority
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
  • 3
LVL 46

Expert Comment

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

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 24249116
Try the value 2

Author Comment

ID: 24251262

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
ID: 24252344
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!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

807 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