Greetings.
Is there a way to send the result of a query to a Calc’s Sheet?
Hello,
Base table or query data is available from registered databases. see this post → Insert data from Base to Calc
For more on this, see the LO documentation → LibreOffice Calc Guide Chapter 10 - Linking Calc Data.
Edit 2020-04-18:
OK, here is a step-by-step.
Base table and Query used:
Calc - get query and drag onto sheet:
This has now created a range:
Now change query (only to show the results will differ):
From menu can see Refresh Range is available:
Click on Refresh Range:
Data updated.
Edit 2020-04-19:
This is abut the last I can help with:
So it shows the registered DB’s. After selecting the query wanted, the result appears in the right panel. Left mouse button hold down when on the upper left grey corner box of the query data, then move mouse pointer to cell where it is to be placed & release left mouse button. It is a simple drag & drop.
Thank you for your help. It kind of solved my problem, for it does not automatically update the sheet, I have to copy and paste.
Best regards
for it does not automatically update the sheet
There was no mention of requiring that in the question. It does in fact automatically update in the sheet when first placed there or refreshed. Don’t understand copy/paste comment.
To get the query’s result into the sheet I have to copy the table’s query into the Sheet below.
What do you mean with “when first placed there or refreshed”?
Take a look at what I’m writing about:
Sorry, based upon this file I have no understanding of what you are asking or actually looking for other than what is presented.
Based upon your question, you have a Base query and you want the result in Calc. The method I presented in the answer gives you that. The Data Source allows you to copy the Query result into Calc. The information is current as to when it is placed in the Calc sheet or if assigned as a Range and refreshed.
I have defined a range but after running the query “Refresh Range” is not accessible.
In addition to the edited information in the answer, if you have multiple ranges defined you need to first select which range is to be updated.
And even more automation with a macro:
Sub RefreshRange
Dim oDatabaseRanges As Object
Dim oRange As Object
oDatabaseRanges = ThisComponent.DatabaseRanges
oRange = oDatabaseRanges.getByName("Import1") REM Your DB Range name
oRange.refresh()
End Sub
which can be attached to a push button.
Can’t say I understand your comment or the link. This situation here is having a single Base query result display on a sheet and refreshing it when data is added to the database.
@Ratslinger - Seems to me this is similar to a filter query. Was wondering if your code could be adjusted to refresh a sheet displaying a filter query without specifically naming query range.
The range reflects the Base query to be executed. There can be many ranges with each connected to a different database query in Base.
Don’t know why you would incorporate a filter query when this is already being sent from the database with the proper selection. All the data is in the database connected to the Base module. This in turn is being called to Calc where it appears some type of chart will be produced. If not for a chart, don’t see any need for Calc to be used at all.
@Ratslinger
I still can’t drag and drop.
I’m going to stick to what I have accomplished so far.
Thank you for all your help.