Run "data to fields" from macro

[Lovro]

Hello, I am automating writer from base by using macros (basic). I already have prepared writer template with connection to database and inserted fields. This procedure is I believe commonly referred to as “mail-merge”, although, I would not call it like that, since I did not use mail-merge wizard, but “Data sources” from view menu and manually chose database fields. Anyway, I would like to populate these prepared fields (database fields) with record obtained from query from database (already set).

So, I just need to know how to programmatically select the first record in a query (it is the only record) and start “data to fields” action from a macro.
Please see image below, the fields which should be populated with data from database are grey-colored.

Thanks!

[Ratslinger]

Place you image in an answer & I’ll merge it into your question. Gave you Karma for image. What type of fields are you talking of - mail merge, text box? How are you connected to DB? How do you intend to run macro? Why not just get data directly from data source?

[Lovro]

Hello, what do you mean by “just get data directly from the source”? I just intend to do that, but since I am automating writer from another application (Base) I need to know how to select a record in a connected query (perhaps this step does not apply when automating?) and to start “data to fields” action. This is just what I do when I do it manually from the writer - first select the record in a query results and then press “data to fields” icon.

[Ratslinger]

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:

Sub PlaceInDocument
	DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
	DataSource=DBContext.getByName("YOUR-DATABASE-NAME")
  '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"
    SQLStatement=DB.createStatement
    Result=SQLStatement.executeQuery (SQLQuery)
    Result.next

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)
	oCellByName.setString(Result.getString)

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:

    DB.close
    DB.dispose
End Sub

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.

[Lovro]

Thank you for this valuable insights. I may need them afterwards. But, as now, I am still trying to use existing mail merge functionality, for the sake of simplicity of creating future documents which should have fields populated from database. I read chapters from Andrew Pitonyak’s book and I better understand TextField and TextFieldMaster concepts.
However, as I understand, only way to populate this fields programmatically is to use mailmerge object and it’s execute method.

[Lovro]

However, I still have trouble with mailmerge, I cannot get it to work, so I will ask another question regarding this.