[Solved] How to copy a query

Discuss the database features
Post Reply
longi
Posts: 110
Joined: Mon Jul 15, 2013 5:04 pm

[Solved] How to copy a query

Post by longi »

I have two problems. The first one is that I know nothing about programming languages. The second one is that I know nothing about English language.
Now, we can start:

I’ve been trying to imitate the next rutine:
1. Go to the queries pane
2. Select one of them (the query you need)
3. Copy it
4. Open a calc sheet
5. Go to the first cell
6. Paste
And you have a calc sheet with all records that the query has!
As you can see in the example, I got all theese steps by code, commanded by a button, but the second step is a tricky step. I use a little WScript(that I didn't do it), in which I have to put the name of the query.
My question is: Can I select a query without a external code?
I’m able to get the queries pane, and I hoped to select the query without point at the queries pane, in order to no bother people’s sight, when they are working.
As I recognised I was not able to do that, I tried to use the window with child window procedures, but my attempts were nulls.
I hope all will be clear. I tried to comment the code line by line, and translated it.

Thanks in advance!
Attachments
Export.7z
(76.42 KiB) Downloaded 192 times
Last edited by longi on Fri Mar 09, 2018 10:31 pm, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How to copy a query

Post by UnklDonald418 »

Make sure your Export database is registered at Tools->Options->OpenOffice Base → Databases.
Open your Calc document and Press F4 to open the Data Sources pane. In the left part of the pane locate your Export database and expand it and also expand the Queries heading and
select the query you want to copy.
The results of the query will now appear in the right side of the pane.
Click on the small empty box at the top left of the query results.
Drag that selection to a cell on your spreadsheet.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
longi
Posts: 110
Joined: Mon Jul 15, 2013 5:04 pm

Re: How to copy a query

Post by longi »

Thanks!
You are answering really fast!
I know this way, but I wanted an authomatic process pushing a button, as already I have. Only I want to know if it is possible to copy the query by code, avoiding external code.
This way could be interesting for me in order to use it in other things.

Thankyou for your answer, but I wanted other one (Sorry!) ;)
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to copy a query

Post by Villeroy »

Drag the query icon from the left pane of the data source window into the sheet.
Now you have a link to the query. See menu:Data>Define... where you find a database range "Import1". Click on it.
In LibreOffice you need to check 2 more options that are checked by default in OpenOffice:
[X] Keep formatting, which means the formatting of the spreadsheet
[X] Insert/Delete cells, which means that cells are inserted and removed as the linked record set grows and shrinks.
Click [Modify] before you click [OK]

Now you can click any single cell in the import range and call menu:Data>Refresh to get a updated copy of row set data.
Any reference to Import1 will refer to the correct range. Adjacent formula columns automatically adjust to the changing size of the row set.

The additional option "Don't save data" only saves the link with no sheet data for that database range. You will be prompted to update when opening the spreadsheet.

One line of macro code to refresh a linked database range: ThisComponent.DatabaseRanges.getByName("Import1").refresh()

More info with example:
[Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
longi
Posts: 110
Joined: Mon Jul 15, 2013 5:04 pm

Re: How to copy a query

Post by longi »

Well!, it seems nobody has a different answer, so I have to put the thread as solved. The answer is right, but at the end is not the answer I wanted.
We can't have all we want!

Thanks so much!
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]How to copy a query

Post by Villeroy »

Store the spreadsheet with the linked import range as template.
Whenever you need a "new copy", open the template, refresh the database range and do what you want with the new file.
You can refresh the database range with a one-line macro and the document-open event.
You can refresh the database range by utilizing the option to not save the linked data. On open you are prompted for the update.
Of course a simple Data>Refresh will do as well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
longi
Posts: 110
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] How to copy a query

Post by longi »

Well!
I got it!
I wanted one different way, only in order to compare with other ways to do the same which are using resultsets (so I put the time function).
I found the "WScript.Shell" tool, and it seems to work properly.
We can change the query's name or the form's name if neccesary.
If it will be used a lot, we can put an input box in order to select a query or another, but for now it is not implemented yet.
I attach the example!

Thanks a lot!
Ah! only proved on Windows!
Attachments
Export2.7z
(76.34 KiB) Downloaded 195 times
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
Post Reply