Macro to import base table into spreadsheet

English
, ,
#1

I can import a base table into a spreadsheet by
(a) opening .odb file and select the table to import
(b) Edit / copy in base
(c) Switch to spreadsheet and click on cell to mark top leftmost part of table
(d) Edit / paste will populate cells in spreadsheet with the imported table.

My question: can this be done with a macro?

I tried to record macro in the spreadsheet but the created macro only performs the Edit/paste with nothing to paste.

Thank you for the opportunity to ask this question,
jankom

#2

Are you aware of the possibility to “refresh” the imported data after the procedure, you described above?
.
Would this be sufficient, or do you really need a full import-macro?

#3

[Tutorial] Using registered datasources in Calc

and more: Apache OpenOffice Community Forum - [Solved] Using Filter in Calc V3.1 with dates - (View topic)

#4

Yes, I’ve tried that. The problem is that I use three tables in one .ods sheet, and Data / Refresh only works for one of the ranges. Doing the formal copy - paste method does it correctly for all three ranges. My spreadsheet, with three tables gives a good overview of the entire situation of the current state of the msql database. In fact those tables are “Views” (really queries).

#5

Yes, the command refreshes the range, where the active cell is in. So all you need is a macro, wich would go to 3 cells and call refresh…
.
If I’m remembering right @Villeroy posted a macro to refresh all database ranges in a sheet sometime.

#6

Wanderer,
Thanks, I tried that, but even if I activated a cell within the range (second or third) the Date/Refresh stayed gray. I tried doing this in separate sheets, even in separate .ods files, creating macro, nothing worked the way it was supposed to. Something is hidden under the hood, a bug, a virus, a secret handshake, who knows. I had a post a few years back in the Apache Open Office site about grayed out Refresh, but I don’t remember if it was for a single range. I worked then. In the mean time I had to switch to new laptop, went from Ubuntu to Mint and Gentoo, changed the .ods file, etc.

If I had some time I’d create a demo msql database file to play with it and to duplicate this problem, and then post it here for solution. I know there is also a difference in behavior between AOO and OLO - I could try that, too.

Presently the brute force copy-paste 3-times works. The Libre/Open Office UNO macro system is rather complicated for my pay grade.

Thanks anyway for your attention, - jankom

#7 
Sub refresh_DatabaseRanges()
for each dbr in ThisComponent.DatabaseRanges
  dbr.refresh()
next
End Sub
2 Likes
#8

In addition to @Villeroy 's great examples, here’s one more.

' Educational example.
' On the fly:
' 1. A data source is created ("C:\temp\Temp.odb")
' 2. A database range (Import_1) is created in a new Calc document.
' 3. The SQL query is executed and the result is placed in the Import_1 database range. 
' Comments tagged ??? can be changed to suit your conditions.
Sub QueryOnTheFly()
  Dim fileName As String, fileUrl As String, DBRangeName As String, sqlQuery As String
  Dim oBaseContext As Object, oDBSource As Object, oDoc As Object, oRange As Object
  Dim props(2) As New com.sun.star.beans.PropertyValue  
   
  '  1. A data source is created ("C:\temp\Temp.odb")  
  fileName="C:\temp\Temp.odb"  ' ???
  fileURL=ConvertToURL(fileName)
  oBaseContext=CreateUnoService("com.sun.star.sdb.DatabaseContext")
  oDBSource=oBaseContext.createInstance()
  With oDBSource
    .url="sdbc:postgresql:host=dragon dbname=ce2pg3"    ' ???
    .DatabaseDocument.storeAsURL(fileURL, Array())
    .DatabaseDocument.Close False
  End With  
  
  ' 2. A database range (Import_1) is created in a new Calc document.
  DBRangeName="Import_1"
  oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
  oDoc.DatabaseRanges.addNewByName(DBRangeName, oDoc.Sheets(0).getCellRangeByName("A1:A2").RangeAddress)
  
  ' 3. The SQL query is executed and the result is placed in the Import_1 database range. 
  oDBSource=oBaseContext.getByName(fileURL)
  With oDBSource
   .User="test3"       ' ???
   .Password="test3"   ' ???
  End With   
  
  sqlQuery="Select * from ""f_ex$clnd"""  ' ???
  
  props(0).Name="DatabaseName" : props(0).Value=FileURL
  props(1).Name="SourceType"   : props(1).Value=1
  props(2).Name="SourceObject" : props(2).Value=sqlQuery
   
  oRange=oDoc.DatabaseRanges.getByName(DBRangeName).referredCells
  oRange.doImport props
End Sub
#9

@sokol and @Villeroy,

Thank you both, looks great. Will try them both and hopefully report as “SOLVED”.

jankom

#10

@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

#11

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.

#12

(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

#13

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.

#14

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.

#15

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?

#16

Sorry, I do use mysql - type. It is in my Mint laptop /var/lib/mysql/ folder.

Image-8W0HG2.png
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

#17

?? 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.

#18

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.

#19

Use latest Libre Office. SOLVED by not using Apache Open Office fork. Thank you all - jankom

#20

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.