How do I open this Macro from the My Macros library?

DaviCaldas · July 2, 2021, 8:22pm

Hi guys, I have this macro in my Libreoffice Base Document:

Sub LimparCampos
if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
ThisDatabaseDocument.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("UPDATE ""Filtro"" SET ""Sintomatologia 01"" = NULL   	")
End Sub

How do I open this Macro from the My Macros library (and not from the document’s own default library)?

Ratslinger · July 2, 2021, 8:44pm

Hello,

The problem is not so much opening in My Macros (place is Standard library or other library then load) but rather that ThisDatabaseDocument is not available there.

See answer(s) in this post → ThisDatabaseDocument vs ThisComponent?

DaviCaldas · July 2, 2021, 9:20pm

Okay, Ratslinger. I understand the difference between ThisDatabaseDocument and ThisComponent. However, when I replace with ThisComponent, the message appears: “ActiveConnection” property or method not found.
When I replace “ActiveConnection” with “isConnected” the same message appears.

Ratslinger · July 2, 2021, 9:42pm

Deleted last comment. Need to do something else first.

Ratslinger · July 2, 2021, 9:54pm

@DaviCaldas,

As noted:

…there is no database at the StarDesktop level.

ThisComponent cannot access database information there.

You can still create a connection with a registered DB or file location or as the comment states pass the object. Personally have not figured out a use for this as it is DB specific. Why not keep in Base?

DaviCaldas · July 2, 2021, 10:02pm

I would like to protect the Macro that cleans up the data fields. I know that it is possible to create a library with a password for the macros. However, I would like to take control of my system and prevent it from being transferred to another computer with all the macros (without my permission).

Ratslinger · July 2, 2021, 10:58pm

I did note three methods.

Pass the database object (easiest)
using registered database name:
using file location. Same as above replacing registered name with file location such as (Linux file):

“file:///home/your_directory/base_file.odb”

Moved code for readability:

sDBName = "REGISTERED_NAME"
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oContext.getByName(sDBName)
oConn = oDB.getConnection("","") 
Stmt = oConn.createStatement()
Stmt.execute("UPDATE STATEMENT")

Also, ThisComponent as referred to in link was accessible based on question information but does not have access to needed database connection. At the wrong level.

DaviCaldas · July 2, 2021, 11:27pm

sDBName = "file:///home/davi/Documentos/Demanda_Espontânea.odb"
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oContext.getByName(sDBName)
oConn = oDB.getConnection("","")
Stmt = oConn.createStatement()
Stmt.execute("UPDATE ""Filtro"" SET ""Sintomatologia 01"" = NULL ")

I changed it but it didn’t work.

Ratslinger · July 2, 2021, 11:48pm

Code was tested before posting and worked. Took your posted code in comment, changed file name/location and minor change to SQL to use my table and no problem.

Don’t know how you are executing or any errors. All you state is that ...it didn't work.

Edit:

Have successfully run this from the IDE and from opening a form.

DaviCaldas · July 3, 2021, 12:11am

Okay Ratslinger, I’ll see try to find out where my error is and then update the forum…

Ratslinger · July 3, 2021, 12:24am

Nothing? Not even where you are executing from? Are you using Standard library in MyMacros?

Edit:

Literally Standard is the name of the library. It is automatically loaded at startup. Others require loading.

Edit 2:

And you have allowed macro execution from that location?

Ratslinger · July 4, 2021, 1:41am

Using a Registered name will use basically the same code. Passing the object also works. Tested from form opening. Code in document (using Open Document event):

Sub sendObject
    if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
        ThisDatabaseDocument.CurrentController.connect
    endif
    Dim oStatement As Object
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    LimparCampos2(oStatement)
End Sub

And in MyMacros:

Sub LimparCampos2(Stmt) As Object
    Stmt.execute("UPDATE_STATEMENT_HERE")
End Sub

All three tested and working.

DaviCaldas · July 4, 2021, 2:26pm

Hi Ratslinger, when I open the document it works perfectly. However, when I execute the “LimparCampos2” sub of a push button inside the form, the message “property or method not found: execute” appears.

DaviCaldas · July 4, 2021, 3:13pm

It worked Ratslinger. The problem was because I had saved the document with an accent (^) and that accent was changing the MACRO. Thank you so much Ratslinger!

FOLLOW THE CODE:

	sDBName = "file:///home/davi/Documentos/Demanda_Espont%C3%A2nea.odb"
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDB = oContext.getByName(sDBName)
oConn = oDB.getConnection("","") 
Stmt = oConn.createStatement()
Stmt.execute("UPDATE ""3. Sintomatologia"" SET ""Tem"" = FALSE  	")