Calc macro failing to update sheet data from Base query

[bbater]

System: Windows 10, LO 6.3.4.2 (x64), Firebird

I have a Calc sheet set up with a data sheet receiving data extracted by a Base query, and a second sheet with a pie chart based upon those data. I want the user to open the spreadsheet and click a [Refresh] button to refresh the data from the query, thus updating the pie chart.

My Calc macro successfully:

1. Clears the data area upon which the chart is based
2. Opens the Data Sources dialog ([Ctrl]+[Shift]+[F4])
3. Navigates to the required query (which runs and updates the associated data area in the Data Sources dialog)
4. Clicks the [Select All] button in the Data Sources data area
5. Right-clicks on the [Select All] button in the Data Sources data area and selects ‘Copy’
6. Pastes the query data into the data area in the sheet
7. Closes the Data Sources dialog ([Ctrl]+[Shift]+[F4])

This all seems to work like magic, except that step 3. is problematic. If the Base data are changed, the data in the data area in the Data Sources dialog are updated as expected, but steps 4. onwards fail to reflect the updates in the data copied & pasted to the data sheet. Rather mystifying.

It feels like a problem which simply requires one further simple action. If anyone can tell me what that is, I would be most grateful.

Rgds,

Bob

OK

++++++++++ Comments added 12/02/2020

Let me run briefly through my test procedure.

Initial data from query used to create Chart:

Change source data by re-assigning Sudanese Nationality to Somali to generate:

Chart expected when data source refreshed:

Error generated on running macro:

Hope that helps.

Rgds,

Bob

[Ratslinger]

Hello,

If this is the only reason to open the spreadsheet, why not just create the chart on a Base form? See → Charts in Base forms.

However, if you need this in a spreadsheet, it will be helpful to get your macros. Have done similar in Calc and Writer but not through a dialog (still looking for old archives I may have on this).

[Ratslinger]

Hello,

Without getting into any of your code, this should refresh the query and update the chart:

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

Have tested with Firebird embedded DB and Calc - LO v6.4.0.3 on Ubuntu 18.04

Up to you but don’t see need to have chart on separate sheet.

Edit:

Sorry for the multiple posts. Modified code as I found items. Probably should have waited till I had all.

[Ratslinger]

Also wanted to mention, there is no need to select, copy and paste the imported data. Once you initially set all should update properly.

Initial setup should be - import data then create chart based on that import. Then with the sheet number (or change to name select if wanted), generated chart name and range name placed into the code all should work.

[bbater]

Thanks Ratslinger. I’ve replaced my (recorded) macro code with yours, named the data range and the chart and referred to the sheet by name thus (my names in bold):

Sub GetNationalityData

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

oRange.refresh()
oAddress = oRange.getDataArea()
sName = "ClientsByNationality"                          REM Your Chart name

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

oCharts = oSheet.getCharts()
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))

End Sub

But when I run the macro, I get an error ‘NoSuchElementException’ on the line “oChart = oCharts.getByName( sName )”

I could send screen shots, but that doesn’t seem to be supported here. Any ideas?

[Ratslinger]

Screen shots are allowed in questions and answers. Edit your question and on the toolbar (upper left in answer) use the image icon (immediate left of paperclip).

The name you should be using can be found in the ‘Navigator’ (F5 to show). It is under OLE objects.

Note: Edited your comment for code clarity - preformatted text on toolbar. Also removed the Bold as that is not allowed in the preformatted section.

[Ratslinger]

Unfortunately images are not always the best to solve a problem. Can you post a copy of the .ods file?

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