Calc macro failing to update sheet data from Base query

[Ratslinger]

In the past somewhere there was another place to input the chart name. Right now I cannot find in v6.4.0.3

Further tests here duplicate the error when the name is inserted as you have. Go back to that and delete the name so it is empty. It is NOT the needed name. With the name field empty get the name from the Navigator. Use that name. Then you can change the name to what you currently have. Should be similar to my Object 1. Will continue to search for the proper location of this internal name.

[Ratslinger]

@bbater,

Posting of the .ods is NOT needed. Previous comment shows the problem and how to get it working. Although you give the chart a name and it appears in the Navigator, internally it is the name originally given at creation. Can’t seem to find in older versions (went back to v5.3.x) but seem to remember it from when I created the Base charts.

[Ratslinger]

So I have discovered where my confusion comes in here. In Writer when you have a chart you can right click & select Properties which presents Object Properties. There you can change the internal name which is also displayed in the Navigator.

To me this is something of a bug in Calc. If a name is entered by the user it is also displayed in the Navigator. However it is never used as the Object name nor is there an easy method to change this. Need to do this via a macro and not certain how other that if the chart is created via macro.

[bbater]

Many thanks Ratslinger, you’ve obviously spent a lot of time on this. I thought that I might just get at the ‘internal’ name for the chart if I deleted the chart and re-created it without renaming it. Navigator then shows ‘Object 1’ to be the name, but inserting this into the code generates the same error. I am tempted to look at your Form suggestion, but the posting you link to sounds like it makes chart creation difficult. I think my users would prefer to work in a familiar spreadsheet environment. I’m going to do some Googling…

[bbater]

Sorry Ratslinger, How do I post the ods file? Editing my question doesn’t display a toolbar.

[Ratslinger]

OK. I tested with your setup. Got your error. Change:

oSheet = ThisComponent.sheets("Chart")        REM Your Chart sheet

to:

oSheet = ThisComponent.sheets.getByName("Chart")     REM Your Chart sheet

Had to redo parts of my sheet to match your naming. Only difference is using ‘Object 3’ in my test.

Forget the .ods again. Try this fix first.

Edit:

Note that this should also work:

oSheet = ThisComponent.sheets.Chart     REM Your Chart sheet

[bbater]

Sorry, same error message. Sheet is named ‘Chart’, Chart is named ‘Chart1’ in Navigator. Here’s my macro code:

Sub GetNationalityData

oDatabaseRanges = ThisComponent.DatabaseRanges
oRange = oDatabaseRanges.getByName("NationalityData")	REM Your DB Range name
oRange.refresh()
oAddress = oRange.getDataArea()
sName = "Chart1"					                                        REM Your Chart name
oSheet = ThisComponent.sheets.GetByName("Chart")                 REM Your Chart sheet
oCharts = oSheet.getCharts()
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))

End Sub

[Ratslinger]

Just copied/pasted your code and only changed sName to ‘Object 3’ and it worked fine!

Is that chart name in navigator without you giving it another name? What happened to ‘Object 1’?

Try ‘Object 1’ for the chart name.

And if this does not work will need the sample file. The toolbar is the same one you used to attach the images. The paperclip icon is what is used to attach the file. Did edit on you question & toolbar is there.

[bbater]

OK, here’s the amended code:

Sub GetNationalityData

oDatabaseRanges = ThisComponent.DatabaseRanges
oRange = oDatabaseRanges.getByName("NationalityData")	REM Your DB Range name
oRange.refresh()
oAddress = oRange.getDataArea()
sName = "Object 1"					                    REM Your Chart name
oSheet = ThisComponent.sheets.GetByName("Chart")        REM Your Chart sheet
oCharts = oSheet.getCharts()
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))

End Sub

This no longer generates an error, but the data are still not being updated. I’ll send you the ods file. Then it’s bed for me, it’s late, here in the UK!

[bbater]

Sorry again. I edit question, but no toolbar! Will need to try again in the (my) morning.

[bbater]

Still get no toolbar when I edit my question. I have sent feedback to ask why. Will get back to you when I can post the ods file.

[Ratslinger]

@bbater,

If this continues as a problem, try using an answer to post. I can then move it to the main question and delete the answer.

[bbater]

@ratslinger By altering data in the data range manually, the chart updates automatically, as it should. I therefore don’t understand why the macro references the chart at all. It seems to me that it should be referencing the data range Import1 and ensuring that it is updated from the Base query when the button is clicked. This is what is NOT happening, despite the documentation giving the impression that a range of data copied and pasted into a Calc sheet is linked by default. I shall pursue further tomorrow. Thanks for all your help so far.

[Ratslinger]

@bbater,

You are possibly missing some information. Yes, the chart will update automatically the values WITHIN the (Chart) RANGE specified. You can actually see this with adding:

Exit sub

After the oRange.refresh() line. All seems to work fine in this case.

With that in place, expand the Base result to have more than 15 lines. Anything beyond B15 will NOT appear in the chart. That is because it’s dimensions are fixed from when the chart was created. The Chart does NOT see the new Range dimensions. That changes when you execute oChart.setRanges(Array(oAddress)). The macro alters the dimensions based upon the amount of data retrieved from Base. It could be less or it could be more.

That is a partial reason why I presented in my comment to you using two different Ranges. Using a single Range AND adjusting the Chart Range via the macro.

So bottom line is that with your setup and never more than 14 lines of data you only need to refresh the Base data - do it from menu.

[Ratslinger]

To me it seems best to cover all bases with the macro instead of relying upon a best guess. Some day the unexpected will happen.

Also worth noting, a chart does not need a named range to be created.

Edit:

Some related posts & bug reports:

tdf#47584

tdf#66250

How do I insert named ranges in charts?

I’ve been working on a series of spreadsheets with the same type of data (they’re outputs of benchmarking data for my videocard), and have been experimenting on how to streamline my workflow. I’ve recently attempted to make named database ranges for each of my 10,000’s-of-rows data columns in lieu of manually selecting them for the data selection part of the chart wizard. Alas, it seems it’s not as intuitive as I first thought. How can I make use of the named ranges I made to easily plug into …

Calc charts: automatically increasing data range

Is it possible to set up a calc chart such that, given a data table that is updated on a daily basis with new items, the chart automatically increases the data range? The problem I’m having is that if I, say, set up a chart with the data range $Sheet1.$A$1:$C$16 but there is only data up to $C$10, the data range will revert to the latter. When the data is subsequently updated and new rows added to the table, the chart doesn’t reflect the changes and forces one to manually: double click the c…

Hope this helps with your understanding of the situation.

[bbater]

OK, I’ve got it working now, using one data range (Import1) and your macro to expand the data range when necessary. It is the case at the moment that the number of values for ‘Nationality’ is fixed at 14 max, but this could change in future. The only residual ‘untidiness’ is that I can’t seem to exclude ‘Nationality_ID’ from the chart when it’s based upon range Import1. But I’ll play around…

Many thanks for your patience and effort with this one, Ratslinger. Your help is very much appreciated.

[bbater]

Solved. Just hide the unwanted column. All fine. Many thanks.

[bbater]

My ods file.STAT_Clients-by-Nationality.ods

Don’t worry - all data is dummy.

[Ratslinger]

OK found the problem. You have two data ranges established. One is “Import1” which is where you get the data from the Base file (Data Source). Then there is “NationalityData” which is what the chart is based upon. You are using “NationalityData” for refreshing the Base data when you should be using “Import1”.

Change this:

oRange = oDatabaseRanges.getByName("NationalityData")	REM Your DB Range name
oRange.refresh()

to:

oRange = oDatabaseRanges.getByName("Import1")	REM Your DB Range name
oRange.refresh()
oRange = oDatabaseRanges.getByName("NationalityData")	REM Your DB Range name

Works for me.

If all goes well this answer can be deleted.

Edit:

Note that had you not included the “ID” in the query from Base this could all be done with one range. With your set-up, you need to refresh one and get the chart data from the other one after the first is refreshed.

[Ratslinger]

After some more thought, the two range method can give you problems. By having just one, and using the original code, the chart is automatically expanded or contracted depending upon the result returned from Base. With your method, you always have these “none” identities and if the return is less than the previous result you may even have erroneous data displayed.