| View previous topic :: View next topic |
| Author |
Message |
spameater
Newbie
Joined: 09 Dec 2004
Posts: 2
|
 Posted: Thu Dec 09, 2004 6:11 pm Post subject: How do you do dynamically updating chart ranges in Calc? |
 |
|
Hi there everybody,
I was wondering how you do dynamically updating chart ranges in Calc?
Just having moved over from Excel and the rest of MS office today everything works fine on my spreadsheets and word documents (horray!) except the dynamically updating ranges!
Here's a very simple example of what I mean. I have a chart/graph that has values over months like the following:
Aug 1st $1000
Sept 1st $1500
Oct 1st $1250
Nov 1st $1750
etc.
Now each month I add new data for example something for December. I want the chart/graph that is being displayed to automatically detect there is new data and include it in the dynamic chart I have created.
Note this isn't a trivial issue for me. Every major spreadsheet I have created has dynamically updating charts, so its critical this works for me(!).
In Excel I would use a process simlar to what is described on this website to do this:
http://www.meadinkent.co.uk/xlgraphoffset.htm
Note this makes use of defined names, function OFFSET and function COUNT in EXCEL. There are equivalent functions offered in Open Office from what I understand.
How do I do this in Calc (dynamically updating charts) and can it in fact be done? Hopefully it can be done!
Thank you for your help! |
|
| Back to top |
|
 |
spameater
Newbie
Joined: 09 Dec 2004
Posts: 2
|
| Posted: Sun Dec 12, 2004 6:15 pm Post subject: ?!? |
|
|
So NOBODY has any ideas?
*sigh*
...
<begins uninstalling OpenOffice> |
|
| Back to top |
|
|
davidh182
OOo Advocate
Joined: 01 Apr 2004
Posts: 413
|
| Posted: Mon Dec 13, 2004 8:03 am Post subject: |
|
|
The following macro will search column B for extent of data and then update chart accordingly:
| Code: |
sub alterchart
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim oCurSheet as object
oCurSheet = StarDesktop.CurrentComponent.CurrentController.ActiveSheet
while oCurSheet.getCellByPosition(0,row).String<>""
row=row+1
wend
dim args2(3) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Name"
args2(0).Value = "Object 1"
args2(1).Name = "Range"
args2(1).Value = "$Sheet1.$A$1:$B$"&row
args2(2).Name = "ColHeaders"
args2(2).Value = true
args2(3).Name = "RowHeaders"
args2(3).Value = false
dispatcher.executeDispatch(document, ".uno:ChangeChartData", "", 0, args2())
end sub |
This assumes data are in columns A and B starting at row 1, and the chart is called "Object 1" - the default name for the first chart. (You can find out the name by recording an action on the chart).
Then you need to trick OO into executing the macro whenever something is added. This can be done by defining a dummy function:
| Code: |
function dummy(rng()) as String
alterchart
dummy=""
end function
|
and setting the function =dummy(A1:B200) in a spare cell
Then whenever a cell in A1:B200 is changed, the function "dummy" will be called which will call alterchart. There may be pitfalls to this approach depending on the exact behviour required... |
|
| Back to top |
|
|
|
