Calc macro failing to update sheet data from Base query

[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.