Ask Your Question
0

Dynamic Contact Information in a Letterhead [closed]

asked 2013-07-17 20:41:41 +0200

khoward gravatar image

updated 2015-11-09 23:58:36 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-09 23:59:05.390569

Comments

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

khoward gravatar imagekhoward ( 2013-07-18 16:47:44 +0200 )edit

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.

khoward gravatar imagekhoward ( 2013-07-18 20:52:14 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2013-07-19 21:29:27 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 '<name>'. 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 ...
(more)
edit flag offensive delete link more
0

answered 2013-09-23 15:23:10 +0200

zenlord gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-07-17 20:41:41 +0200

Seen: 609 times

Last updated: Sep 23 '13