[Solved] Chart - Modify referenced sheet

[kebab4you]

Did some reading and there doesn't seem to be any way that I can get a chart to reference the current sheet it's located on through the normal interface. What I would like to do is after I copied a sheet with the copyByName() function to loop through all charts on the copied sheet and tell them to reference the copied sheet instead of the original. Looping through all the charts I know how to do but I can't figure out how to modify what sheet the chart reads from.

[MTP]

You'll probably need to poke around inside the chart object with Xray or MRI ([Tutorial] Introduction into object inspection with MRI) to figure out what property to change.

Graphs are actually really complicated objects. You might get some ideas from the few examples of creating or editing them with macros on these forums:
Code example for XY chart (original)
Code example for bar chart (modified from XY code)

[Villeroy]

Do you know scenarios? http://forum.openoffice.org/en/forum/do ... hp?id=3004 (One chart with exchangable source ranges)

[kebab4you]

You'll probably need to poke around inside the chart object with Xray or MRI ([Tutorial] Introduction into object inspection with MRI) to figure out what property to change.

Graphs are actually really complicated objects. You might get some ideas from the few examples of creating or editing them with macros on these forums:
Code example for XY chart (original)
Code example for bar chart (modified from XY code)

Thank you, will take a look at it.

Do you know scenarios? http://forum.openoffice.org/en/forum/do ... hp?id=3004 (One chart with exchangable source ranges)

Yeah read about it from another topic that you had commented on. From the sounds of it it's something the user have to activate for each sheet which won't work for me because I'm dealing with another end-user.

[Villeroy]

Yeah read about it from another topic that you had commented on. From the sounds of it it's something the user have to activate for each sheet which won't work for me because I'm dealing with another end-user.

There is only one visible sheet with one chart and one source range reflecting different data sets you can choose from a list box or from the navigator tool.

[kebab4you]

Solved it in quite an easy matter with the following:

Code: Select all

Do While I < 2
		oChart = oCopy.Charts.getByIndex(I)
		Ranges = oChart.Ranges
		Ranges(0).Sheet = oCopy.RangeAddress.Sheet
		oChart.Ranges = Ranges
		I = I+1
	Loop

Haven't found a way to get a count on all charts that are on the page yet so I'm using the hardcoded value of 2. Since I only have two charts at the moment.

[Villeroy]

Well yes, that's what scenarios do without a single line of Basic code
or if you want to:

Code: Select all

objSheet.Scenarios.getByName("Plan_B").apply()