macro to automate data to fields in Writer

[PEcho]

I am using a Base database to manage information about projects. The documents that get filled from database queries all work - pretty much. I want to be able to have a macro that, when the document opens asks whether to fill, if the user clicks “yes” then all the fields populate automatically from multiple queries.

Why this is useful: Currently, I have several documents that rely on two, three or four queries. One with master project information, a second with team members and their assignments, a third with team leaders and resources assigned, and another with budget line items and totals. Doing this manually requires (1) open data sources, (2) select the appropriate query, (3) select all records in the table, (4) click “data to fields” to populate the data, (5) repeat 2 - 4 for all queries. It seems like this should possible to automate.

Using LO 5.1 in Ubuntu 15.10 (soon to be 16.04

[Ratslinger]

The answer to your question is it can be automated using macro(s). The actual macro code is the problem. There is no “universal” macro to perform what you want. It must be specifically written to your situation.

(1) Open data sources - this is just setting a connection to your database.

(2) & (3) Would be one step - executing the query in the macro producing a Result Set. That is each field of each record.

(4) Data to Fields - This is where the work comes in. Each data field of each result record must either be moved to an already defined field in the document (as you may already have) or the cursor can be positioned using code and the result pasted into the document.

Now all this must be done for each query you have and for each document you have. What would need to be done is for someone to analyze what you have and the best way to get the result you want. Based upon the information provided, this amount of coding is beyond the scope of a forum answer.

For an example, here is a macro which connects to a database, runs a query and moves the first field of the first record to the current cursor location:

Sub PlaceInDocument
   DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
   DataSource=DBContext.getByName("DatabaseName")
   'Needed if DB is PW protected'
   UserName=InputBox("Enter user name", "Attention")
   UserPassword=InputBox("Enter password", "Attention")
   DB=DataSource.GetConnection (UserName, UserPassword)
   SQLQuery="SELECT * FROM SOMETABLE"
   SQLStatement=DB.createStatement
   Result=SQLStatement.executeQuery (SQLQuery)
   Result.next
   oViewCursor = ThisComponent.CurrentController.getViewCursor()
   oViewCursor.getText.insertString(oViewCursor.getStart(), Result.getString(1), False)
  DB.close
  DB.dispose
End Sub