Help converting Excel VB code to LS

HCL Domino Domino Forum
1

Hi Notes World,

I’m trying to learn how to create Excel graphs using LotusScript. From going through the forums, I found that recording the macro in Excel is a great way to generate the code for use within LotusScript. But I have a bit of a problem. The code below was created by the macro recorder…

Range("A2:F7").Select

Range("F7").Activate

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:F7"), PlotBy:= _

    xlRows

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

With ActiveChart

    .HasTitle = False

    .Axes(xlCategory, xlPrimary).HasTitle = False

    .Axes(xlValue, xlPrimary).HasTitle = False

End With

My question is, in lines like…

ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(“A2:F7”), PlotBy:=xlRows

or

ActiveChart.Location Where:=xlLocationAsObject, Name:=“Sheet1”

Most of the code I can convert into LotusScript, however these lines I don’t know how to convert to LS. Using …

Source:=Sheets(“Sheet1”).Range(“A2:F7”), PlotBy:=xlRows

or

Where:=xlLocationAsObject, Name:=“Sheet1”

does not follow syntax rules in LS. What is the correct way to convert these lines to LS?

Thanks

2

Subject: Help converting Excel VB code to LS …

Hey,

Sorry I can’t be much help on this. However, I ‘thought’ when you were using the API you would leave the call similar to how it is structured in VBS (What you have)?

Here is the link to the API documentation on MSDN.

cheers,

Shawn

3

Subject: Help converting Excel VB code to LS …

I posted this to the old 4/5 forum about 5 years ago…it’s linked in the FAQ of FAQs…

http://www-10.lotus.com/ldd/46dom.nsf/DateAllFlatweb/dc09168723293c9885256b9e004dd58b?OpenDocument

hth.

brandt

4

Subject: Help converting Excel VB code to LS …

Excel function can have their parameters passed by Name (as in your example) or by Position (as in LotusScript).

You’ll need to look up the functions in the Excel help to see which positions those named parameters should be in, and reconstruct the call using the positional syntax.

5

Subject: Help converting Excel VB code to LS …

From the MS Excel help…

SetSourceData Method

See AlsoApplies ToExampleSpecificsSets the source data range for the chart.

expression.SetSourceData(Source, PlotBy)

expression Required. An expression that returns a Chart object.

Source Required Range. The range that contains the source data.

PlotBy Optional Variant. Specifies the way the data is to be plotted. Can be either of the following XlRowCol constants: xlColumns or xlRows.

Example

This example sets the source data range for chart one.

Charts(1).SetSourceData Source:=Sheets(1).Range(“a1:a10”), _

PlotBy:=xlColumns

So I’m guessing that if you just use the brackets and make sure the parameters are in the right order it should work.

hth

Pete