Dynamic Contact Information in a Letterhead

Hello everyone!

My name is Keith Howard.

Tech info:
OS: WinXp Sp3
LO Ver: 4.0.4.2

Let me, first, attempt to explain what I want to accomplish.

As the subject of this post implies, I have a letterhead (writer document) with contact information in the header. I want to dynamically update the contact information from a database (LDAP technically, but I have that part setup).
I have the connection setup, and I have a query that limits the row to one. The query is such that it limits by Job Title. So, if I want a letterhead for the manager, I setup the query and link it to this letterhead. If the manager is replaced (or name changes), I want the information to be reflected, DYNAMICALLY (read, no user interaction).

I’ve tried mail merge fields, but pressing F9 does nothing and the user would have to go through the mail merge wizard.

I’ve, also, tried F4-select the row-then press (Data to Fields) button in the toolbar.

This works great, but I would like to automate this with a macro, as it is too many steps for the user.

So, my question(s) is/are, am I going about this the right way by using Mail Merge fields or is there a different/better/easier way?

If Mail Merge fields are the best way to go, how can I create a macro that emulates pressing F4, selecting the one row and then pressing ‘Data to Fields’?

I’ve, also, tried this StarBasic command in a macro without success (it may not exist?):

dispatcher.executeDispatch(document, ".uno:DataToFields", "", 0, Array())

Thanks!

Keith

I should note that I tried recording a macro and can emulate pressing F4 which reveals the data browser, but nothing more. The ‘record macro’ function does not catch me selecting the row and pressing the ‘data to fields’ button in the data browser toolbar. I need to know how to select the row (result set) and then execute ‘data to fields’ via a macro…
Or a different solution to my problem is also most welcome!

Thanks!

Keith

I tried using form text boxes. I changed the ‘Data Field’ on the ‘Data’ tab of the ‘Control’ property sheet to reflect the appropriate column. This works perfectly, except that it doesn’t seem to be possible to insert the control into the header. It looks like it’s in there, but it is not. If someone types a very long document using this letterhead, and it must continue on a second sheet, the form fields do not replicate in the header of the second page.

Create a LibreOffice (LO) Base document and connect to your database as usual.
Let LO register it for you.
Create a query ( I named mine CBO) that gets the data you need, for instance:

SELECT "Display Name", "Job Title", "Phone (Work)", "E-mail" FROM "LDAP Directory" WHERE "Job Title" LIKE 'Chief%'

Save the query and the database.
Next, create (or use an existing one) a Writer doc and create some variables like this:

  • Press ‘CTRL+F2’ to open the ‘Fields’ property window
  • Next, click the ‘Variables’ tab
  • Now, in the listbox named ‘Type’, select ‘User Field’
  • In the 'Format" list box choose ‘text’
  • Next, towards the bottom in ‘Name’ type the name you want to call your variable…lets use ‘Name’. I would advise against spaces in your variable names.
  • In the ‘Value’ box, type in something like ‘’. This is just placeholder text. You can type anything, but it’s best not to leave it blank, lest you not see it displayed on the page before the macro runs. I will stick with the variable name enclosed in angle brackets.
  • Press the blue arrow to save variable definition
  • Do the same to define variables for title, phone, and email.

We will use these variables later in LO Basic Macro code.
Assign the database you first created above to this document.

  • Click ‘Edit’ in the main toolbar and choose ‘Exchange Database’.
  • Under ‘Available Databases’ choose the name of the database that you created earlier.
  • Click the plus sign next to it and choose the query from earlier (CBO).
  • Now click ‘Define’.

Save your document.
Create the macro that will update these variables.
Click ‘Tools’->‘Macros’->‘Organize Macros’->‘Libre Office Basic’.
Click on the plus sign next to your document name, then click on ‘Standard’. Click the ‘New’ button and name it ‘dataToFields’. Now click edit. Copy and paste this code:


Sub main
dbContext = CreateUnoService(“com.sun.star.sdb.DatabaseContext”)
DB = dbContext.getByName(“ldapDB”) ‘This should be the registered name of your db’
connection = DB.getConnection("","") ‘get connection’
query = connection.Queries.getByName(“CBO”).command ‘get the sql query text (command) from the query object stored in the db called CBO’
statement = connection.createStatement() ’ create a statement object to run the query’
res = statement.executeQuery(query) ’ execute the query and store it in res, result set’
doc = ThisComponent ‘get a var set to this doc’
fm = doc.getTextFieldMasters() ‘get the field ( doc variables) names’
context = “com.sun.star.text.fieldmaster.User.” ‘the context in the LO api for user defined fields…saves us typing’
Do While res.next ()
meta = res.getMetaData() ’ ? dunno what this does’
cur_row = res.getRow () ’ get the current row in the resultset’
field = fm.getByName(context & “Name”) ’ get the doc variable name (by name) and save as field’
field.Content = res.getString(1) ‘set the field value, the doc variable, to the first column of the result set’
field = fm.getByName(context & “Title”)
field.Content = res.getString (2)
field = fm.getByName(context & “Phone”)
field.Content = res.getString (3)
field = fm.getByName(context & “Email”)
field.Content = res.getString (4)
doc.TextFields.refresh() ‘refresh the page to show updates’
Loop
End Sub

Save the macro and exit the macro editor.
Now goto ‘Tools’->‘Customize’ and click on the ‘Events’ tab, then choose ‘Activate Document’.
Now, towards the top, under ‘Assign’, click ‘Macro’.
Click the plus sign next to the documents name, then ‘Standard’ then ‘dataToFields’.
Click ‘ok’.
Save the document.

For a connection that doesn’t require an .odb file:


Sub main
   doc = ThisComponent 'get a var set to this doc'
   fm = doc.getTextFieldMasters() 'get the field ( doc variables) names'
   context = "com.sun.star.text.fieldmaster.User." 'the context in the LO api for user defined fields....saves us typing'
   Dim manager,connection,statement as Object
   Dim query$ as string
   Dim properties(1) As New com.sun.star.beans.PropertyValue
   Dim quotes as String
   quote = """"
   manager = CreateUnoService("com.sun.star.sdbc.DriverManager")
 
   properties(0).Name = "HostName"
   properties(0).Value = "server"
   properties(1).Name = "BaseDN"
   properties(1).Value = "ou=Users,ou=bldg,dc=medinaco,dc=net"

   connection = manager.getConnectionWithInfo("sdbc:address:ldap",properties())
   statement = connection.createStatement()
   query = "SELECT " & quote & "Display Name" & quote
   query = query & "," & quote & "Job Title" & quote
   query = query & "," & quote & "Phone (Work)" & quote
   query = query & "," & quote & "E-mail" & quote
   query = query & " FROM " & quote & "LDAP Directory" & quote 
   query = query & " WHERE " & quote & "Job Title" & quote & " LIKE 'Chief'"
   print query
   res = statement.executeQuery(query)
   Do While res.next ()
	meta = res.getMetaData() ' ? dunno what this does'
	cur_row = res.getRow () ' get the current row in the resultset'
	field = fm.getByName(context & "Name") ' get the doc variable name (by name) and save as field'
	field.Content = res.getString(1) 'set the field value, the doc variable, to the first column of the result set'
	field = fm.getByName(context & "Title")
	field.Content =  res.getString (2)
	field = fm.getByName(context & "Phone")
	field.Content = res.getString (3)
	field = fm.getByName(context & "Email")
	field.Content = res.getString (4)
    doc.TextFields.refresh() 'refresh the page to show updates'
   Loop	
End Sub

I tackled a similar problem the other way around: I added a function to my web application (written in PHP) that relies on TBS and the plugin OpenTBS, which is an open source object oriented PHP templating app to make (Libre/Open/MS)Office documents from a template.

I still use basic macro’s for printing the document to different printertrays, but I rely on an outside script to fill in all the dynamic content into the new document, coming from both PostgreSQL and OpenLDAP.