Ask Your Question

Is it possible to output cell values directly to base or MYSQL with macro [closed]

asked 2017-02-15 15:37:35 +0200

Neil-B gravatar image

Hi All

I have a Calc spread sheet which i use for my invoicing, on that I have a "save"button that as well as saves the invoice to the appropriate folder it also opens a text file with the next invoice number with open for input to read the invoice number to apply' it then updates the file with open for output and saves the new invoice number for next time.

is it possible to use something like "open for output" to a base file or even better a MYSQL database as part of the save macro

I am only looking to save things like the invoice date for instance (A1) and invoice amount (B2)

I have a base file connected to MSQL for this information but i have to manually input it after i save the invoice which is prone to errors

If someone could point me in the right direction I would much appreciate the help

Many thanks Neil

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-24 21:20:16.104563

1 Answer

Sort by » oldest newest most voted

answered 2017-02-15 20:41:57 +0200

Ratslinger gravatar image

updated 2017-02-16 00:19:00 +0200

This routine works from any DB to another (utilizing Base) or from Calc to Base attached to MySQL (all tested):

Sub UpdateSQL
    Dim Context as Object
    Dim Db as Object
    Dim Conn as Object
    Dim Stmt as Object
    Dim strSQL as String

    rem Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("")

    rem URL of database (it is also possible to use a registered DB name)
    Db = Context.getByName(DATABASE_NAME)

    rem connect to the database
    Conn = Db.getConnection("","") 

    rem create statement object
    Stmt = Conn.createStatement()

    rem compose SQL statement, retrieve your data from sheet and insert your data
    rem Example "Insert" statement; construct as appropriate or create "Update" statement
    strSQL = "INSERT INTO YOUR-TABLE (NAME,NOTE) VALUES ('Input 95','New Record91');"

    rem execute SQL statement

    rem close the database connection

    rem notify the user
    msgbox("table updated")
End Sub

As noted in the remarks, what and how (fields, Insert/Update) are up to you as well as where you get the data from.

edit flag offensive delete link more


Should have mentioned: prior to Conn = Db.getConnection("","") you would want a prompt for user & password (or hardcode if wanted) to end up with Conn = Db.getConnection(MYSQLID, MYSQLPW)

Ratslinger gravatar imageRatslinger ( 2017-02-15 20:53:38 +0200 )edit

Hi Ratslinger,

Thank you for your answer, that looks like what i am looking for, I will give it a try in the next day or so.

Many thanks Neil


I have "Rem"ed out the compose the sql statement section, mainly because I haven't a clue how to form the statement in the first place, Here comes another learning curve.

but i it seems to connect with my data base fine, so again many thanks for you help


Neil-B gravatar imageNeil-B ( 2017-02-16 09:38:24 +0200 )edit

Question Tools



Asked: 2017-02-15 15:37:35 +0200

Seen: 709 times

Last updated: Feb 16 '17