While it may seem relatively easy (select a record then
Data to Fields action), doing the same in a macro is more complex. All the code & discussion following can be found in Andrew Pitonyak’s book OOME (Open Office Macros Explained) available here as a pdf.
There are multiple ways to get data from a database and place the info in a document using a macro. The first step is to get the information as a result set through a query:
'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 YOUR-TABLE-NAME"
Then you use the result set information and place it where wanted through various means. This places the first field of the first record at the current cursor position:
oViewCursor = ThisComponent.CurrentController.getViewCursor()
oViewCursor.getText.insertString(oViewCursor.getStart(), Result.getString(1), False)
The above requires setting the cursor (through more code) for each field to be moved
Or, if using tables (as you have), you can move it into a table cell:
Dim oDoc As Object
Dim oTextTables As Object
oDoc = ThisComponent
oTextTables = oDoc.getTextTables()
'Get first table'
oObj1 = oTextTables.getByIndex(0)
'Get cell in Row 2 Col 2'
oCellByPosition = oObj1.getCellByPosition(1, 1)
This requires accessing each table and cell in the table to move data into.
In your sample you not only have tables but also text fields (or in your case master fields). Master fields are text fields with names (see section 14.10.1 of the above mentioned book OOME for more details). In this particular setup, you need to obtain a particular master field and then move the result set data to that field.
And when all the data has been moved, you need to shut down the connection to the database:
There are more ways but if you really want this “automation”, it seems the table method may be the easiest approach. Any way chosen depends upon specifics within your database table and your writer document.
When you have a completed routine, depending upon its’ purpose, you need to trigger the macro either through a push button, a toolbar item or probably a document event.