Ask Your Question
0

Calc macro failing to update sheet data from Base query

asked 2020-02-11 02:30:14 +0200

bbater gravatar image

updated 2020-02-12 03:31:50 +0200

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: image description

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

Chart expected when data source refreshed: image description

Error generated on running macro: image description

Hope that helps.

Rgds,

Bob

edit retag flag offensive close merge delete

Comments

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 gravatar imageRatslinger ( 2020-02-11 03:50:22 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-02-11 05:05:15 +0200

Ratslinger gravatar image

updated 2020-02-11 05:18:56 +0200

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.

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2020-02-11 07:04:21 +0200 )edit

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?

bbater gravatar imagebbater ( 2020-02-12 02:28:32 +0200 )edit

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 gravatar imageRatslinger ( 2020-02-12 02:45:08 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2020-02-12 03:52:57 +0200 )edit

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 gravatar imageRatslinger ( 2020-02-12 04:16:22 +0200 )edit

@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 gravatar imageRatslinger ( 2020-02-12 04:51:44 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-02-12 05:39:01 +0200 )edit

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 gravatar imagebbater ( 2020-02-13 00:55:40 +0200 )edit

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

bbater gravatar imagebbater ( 2020-02-13 02:35:16 +0200 )edit

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
Ratslinger gravatar imageRatslinger ( 2020-02-13 02:46:05 +0200 )edit
0

answered 2020-02-14 01:09:27 +0200

bbater gravatar image

My ods file.C:\fakepath\STAT_Clients-by-Nationality.ods

Don't worry - all data is dummy.

edit flag offensive delete link more

Comments

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 gravatar imageRatslinger ( 2020-02-14 01:41:50 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-02-14 02:35:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-02-11 02:30:14 +0200

Seen: 49 times

Last updated: Feb 14