System: Windows 10, LO 220.127.116.11 (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:
- Clears the data area upon which the chart is based
- Opens the Data Sources dialog ([Ctrl]+[Shift]+[F4])
- Navigates to the required query (which runs and updates the associated data area in the Data Sources dialog)
- Clicks the [Select All] button in the Data Sources data area
- Right-clicks on the [Select All] button in the Data Sources data area and selects ‘Copy’
- Pastes the query data into the data area in the sheet
- 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.
++++++++++ 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.