[Solved] Insert Chart On Separate Sheet USING OOBasic Macro

[peter.ant]

I'm using Calc, OpenOffice 4.1.2. I use an OOBasic Macro to create a new sheet named "Charts" (easily enough done), create some charts ("easily enough done"), and want to place/arrange them neatly in the new sheet.

When I create a new Chart ("MyChart") and add it to all the Charts in the spreadsheet:
Charts = Doc.Sheets(0).Charts
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
... it appears in the first sheet of the spreadsheet, not in the new sheet I just created.

Thinking that Doc.Sheets(0) might be the problem (the zero selecting the first sheet), I tried:
Charts = Doc.Sheets("Charts").Charts ' Look for the Charts sheet by name
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
' I also tried Doc.Sheets(2).Charts, since that's the index of the "Charts" sheet.

I have tried making the new ChartSheet the 'active sheet':
Sheets = Doc.Sheets 'get the container of all Sheets
ChartSheet = Sheets.getByName("Charts") 'get the sheet named Charts
Controller = Doc.getcurrentControllerController.setActiveSheet(ChartSheet)
The new sheet is displayed to the user, but the charts are still written into the first sheet.

So the question is: how can I specify which sheet a chart is placed in?

Thanks for any help.

[FJCC]

Using
Charts = Doc.Sheets(2).Charts
should work as should
Charts = Doc.Sheets.getByName("Charts").Charts

If they don't, please upload a small example file containing your macro. There is an Upload Attachment tab just below the box where you type a response.

[peter.ant]

This SpreadSheet has two sheets with data. I want to add a third sheet and place some charts in it.

The spreadsheet also had an extra sheet with some "junk" in it. When I removed that sheet and used indexed addressing to specify the sheet to place the chart in (see bold line below), it worked!

Update. I noticed my error when trying to get the sheet by name. When I used the code you suggested:
Charts = Doc.Sheets.getByName("Graphs").Charts
That works too! I don't know if removing the "junk" sheet was relevant or not. It shouldn't be, but my original code did use "indexed addressing" correctly ...

Thanks for your help.

Sub Main
Dim Doc As Object
Dim GraphSheet As Object
Dim Sheets as Object
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Doc = ThisComponent

'Delete and re-add the Graphs sheet to clear it. Probably a better way.
If Doc.Sheets.hasByName("Graphs") Then
Doc.Sheets.removeByName("Graphs")
end if

GraphSheet = Doc.createInstance("com.sun.star.sheet.Spreadsheet")
Doc.Sheets.insertByName("Graphs", GraphSheet)
' Charts = Doc.Sheets(0).Charts '<- 0 is probably the culprit
' Charts = Doc.Sheets("Graphs").Charts '<- Nope, no dif. Graph is written into Data sheet.
Charts = Doc.Sheets(2).Charts '<- Nope, no dif. Graph is written into Data sheet.

Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 1
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 2
RangeAddress(0).EndRow = 12
' Where is this chart displayed? Data!
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
End Sub