@sokol and @Villeroy,
Thank you both, looks great. Will try them both and hopefully report as “SOLVED”.
jankom
@Villeroy - tried the macro, it would work, but only for one range.
The bug (or whatever) is
(a) when I drag the second table into a cell in the spreadsheet it adds another row (duplicates the last row). I can delete the duplicate cells, redefine the range, but then the range is not recognized by the macro - or Date/Refresh is grayed out.
(b) the table dragged into the third range (table to occupy the cells) pushes the neighboring cells down. OK, so I put the 3rd table somewhere else, but it still added another row as in (a)
This is where I spent a lot of time trying to drag each table to a different sheet or even to a different .ods file.
Something is wrong with dragging more than one table from f4 to the spreadsheet.
jankom
I don’t understand what you are trying to do.
We don’t know which kind of database you are using.
You don’t even tell us your operating system, let alone your office version.
(1) What I’m trying to do - copy three tables from a msql database and refresh them occasionally as in my initial question I can do that, but would like to do the refresh with a macro. The f4, Data/Refresh works if only one table is “imported” into a Data Range.
(2) msql
(3) linux (Mint, Gentoo, two computers, networked), AOO current (4.1,14). As I mentioned in a previous post I could reinstall latest LibreOffice and see if this problem is the same. In that case my question would be mute, the solution would be go with the Oracle flavor of Office. I understand that, and will investigate when I have some time.
jankom
What’s wrong with the database ranges? They resize automatically, expand/shrink adjacent formula ranges and update references in formulas, charts and elsewhere. In order to do so, they need to insert/remove rows and columns as needed.
I’m trying to test this with Oracle Libre Office; installed in my linux Mint laptop, but now cannot connect to database: orgmariadb.jdbc_Driver is missing. I can still connect from my Gentoo machine running Apache flavor of Office.
So you are not using mSQL - Wikipedia
LibreOffice comes with a native driver for MySQL and MariaDB which are more or less the same.
You can install the JDBC driver under Tools>Options>Advanced [Class Path]
ODBC is another option.
But what was wrong with database ranges and how did you test them if you can not connect your database?
Sorry, I do use mysql - type. It is in my Mint laptop /var/lib/mysql/ folder.
I tested it with Apache flavor on my Gentoo machine,and had the problems I mentioned. So now I try to test it with Oracle flavor on the Mint laptop and cannot connect.
The data source is on the Mint laptop, and the Gentoo machine is networked to it.
What should I put in the class path? I thought the installation took care of it.
Thanks for your continued attention - jankom
?? StarDivision created StarOffice, wich was acquired by Sun and released as OpenOffice. When they sold to Oracle and development was not trusted by parts of the community LibreOffice was forked from OpenOffice (wich was later given to Apache as ApacheOpenOffice AOO). So unless you use a ridiculous old version there is no “Oracle flavor”.
Then from Mint you should use the same setup, but for localhost, but the same name or IP, wich is used from Gentoo should work also.
Yes, OK, understand.
In fact, after some searching I installed the jdbc driver. Now I can connect, tested with the newly installed OLO and everything worked as expected.
This question has been answered by this community. The macro works, sorry for using the Apache flavor of Star Office. I think the problems I encountered with Apache flavor has to do with gui, compositor, mime, etc.
Yes, thank you, SOLVED!
jankom
P.S. I’ll mark it solved in the web.
Use latest Libre Office. SOLVED by not using Apache Open Office fork. Thank you all - jankom
Sorry to bother this community again, but base connect is not working again. All I did was to shut down the laptop last night. This morning I tried to use my .odb file, and connection error showed up.
At least I can still use the mysql database from the networked Gentoo machine running apache flavor of Office. The laptop has the Oracle flavor Office, but cannot connect.
Yes, the Libre Office (Oracle’s version of the sold Star Office) is actively maintained, solved my Data Import problem with macro, but now I cannot connect.
OK, I realize it is “free”, or “open”, and I’m willing to help this community as best as my ability permit, but this is frustrating. Probably something I did.
I started to use Star Office about 30 years ago, ever since I started learning linux.
jankom
menu:Edit>Database>Properties…
In case of JDBC: menu:Tools>Options>Advanced (or Java in OO), button [Class Path]
The driver class name is “com.mysql.jdbc.Driver” in the database properties
Can you open this database?
Sorry for the long delay and thank you for your help. In fact I did not get notification of your answer. After the holidays I started a new question in the same subject and finally the connection problem was resolved, see Cannot connect to mysql - #8 by RobertG
To answer to your question: NO, I cannot open “this database”. It has to do with the embedded database, see the other post referenced above. As mentioned in the other post, such file can be opened in OO in my Gentoo desktop, including your “this database”.
jankom
menu:Help> About LibreOffice…
Click the copy button in the middle and paste here.
For future reference @Villeroy is referring to the icon clearly shown in your screen capture next to the text Version Information. As was mentioned, you can click that icon to copy the version information below it.
Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.4
Calc: threaded
OK, I changed the name by removing “cj”, and it still connects with my other ODB files. However, your PowerFilter.odb would not connect. In fact, if I open that file Edit / Database … has only the third selection available (Advanced settings), the other two are grayed out. I edited the driver class name with another odb file but I still cannot show tables in your odb file:
jankom