Open Life

Ok let's get this first stated: I am not a developer. I am a blood sucking leach feeding on the work of others. I don't produce code, I just modify it enough to fit my needs.

Once that said, you can argue that I am just in learning mode so eventually I will be part of the producting part of the FLOSS society and produce code from my own. Meanwhile I know enough to use code from other people to rearange my work and automate it.

I currently work in an enviroment full of hardcore techies, they love their bash scripts. However just like any other techie, they run away from documentation. I have been the only brave one to come up with some backbone and get on the challenge to document each of our 200 servers.

This however become easier task as I made an OpenOffice.org script to automate the task of scripting. However if you put attention earlier, I am not a programmer, however I only know enough to get the core code I need to get to my goal.

This is when I went to one of my community buddy's website Pitonyak.org and took a look at his cookbook which is full of snippet of code to do almost anything in OpenOffice.org through the use of OOoBasic.

This code include very interesting stuff such as:

• Variable, Arrays and functions
  
• UNO and Interfaces
  
• Printing documents
  
• Changing language of OOo
  
• Load a document from a URL
  
• Graphic manipulation, etc
  

This is just the begining of a 500pages long document with all kind of snippets. So how did this help me? Well I just start gluing this snippets and create the script I needed to automate my work.

The Document
The documentation consisted on a s series of spreadsheet documents full of data. Each sheet will be dedicated to each server, and each file will be dedicated for each account. The whole documentation consisted on at least 4 accounts.

Fortunately the servers were somewhat the same, they had the same profile and just duplicating the page will be enough. However, there was a problem.

The duplication of pages took too much, a series of clicks were needed and in the end the sheet still needed to be update for the righ server.

A mail merge will be so good in this case however the way the documentation was worked on, mail merge was not obvious.

Here is where OOoBasic came and Andrew's Pitonyak book was fundamental.

The script
The script language that OpenOffice.org uses by default is OpenOffice.org Basic. This language is similar to Microsoft VBScript, however it uses OpenOffice.org native API called UNO.

So first I needed to do is put a set of goals that the script needed to achieve and separate it into steps:

• Select all the document
  
• Being able to copy it
  
• Generate a new document
  
• Paste into the document
  
• Generating a new sheet
  
• Make the sheet name consistant
  

The next step beside duplicating the sheet was generating another script to what I called 'labeling the sheets'. This label process also had different steps that will follow another logic, similar in esence but the script actually change a lot.

• Being able to select a specific cells
  
• Insert a text into the cell
  
• Load the data into a datasource just like an array
  
• being able to extract that data from another spreadsheet which listed the server names.
  

Lets get to work
Ok boys and girls, the first step is to open the OOoBasic IDE. To open this, you need to go to Tools > Macros > Organize Macros > OpenOffice.org Basic. That will open the IDE and start to write the code.

First I went to Andrew's script on duplicating a file. This was not the code I needed exactly but it got close enough. This script will generate a new document. The topic number 6.18.1 Copy entire sheet to a new document.

'Author: Stephan Wunderlich [stephan.wunderlich@sun.com] 
Sub CopySpreadsheet
  firstDoc = ThisComponent
  selectSheetByName(firstDoc, "Sheet2")
  dispatchURL(firstDoc,".uno:SelectAll")
  dispatchURL(firstDoc,".uno:Copy")
  secondDoc = StarDesktop.loadComponentFromUrl("private:factory/scalc","_blank",0,dimArray())
  secondDoc.getSheets().insertNewByName("inserted",0)
  selectSheetByName(secondDoc, "inserted")
  dispatchURL(secondDoc,".uno:Paste")
End Sub

Sub selectSheetByName(document, sheetName)
  document.getCurrentController.select(document.getSheets().getByName(sheetName))
End Sub

Sub dispatchURL(document, aURL)
  Dim noProps()
  Dim URL As New com.sun.star.util.URL

  frame = document.getCurrentController().getFrame()
  URL.Complete = aURL
  transf = createUnoService("com.sun.star.util.URLTransformer")
 transf.parseStrict(URL)

  disp = frame.queryDispatch(URL, "", com.sun.star.frame.FrameSearchFlag.SELF _
         OR com.sun.star.frame.FrameSearchFlag.CHILDREN)
  disp.dispatch(URL, noProps())
End Sub

So lets analyze this script, first thing to see is divide this code into the different processes. Here we see 3 different subprocesses, first is CopySheet, SelectSheetByName and dispatchURL.

• CopySheet - this is the logic of the script that will use the other two functions.
  
• SelectSheetByName - this is just a function that will encapsulate the UNO components and put the parameters such as sheetname and getsheet.
  
• dispatchURL - Also encapsulated uno method such as the URLTransformer to generate new documents and also the 'frame' object.
  

Having figure out this two functions is obvious we need to focus on the first sub process. First we need to get rid of the 'new document' and direct everything to the first document. So first thing was replacing the secondDoc with firstDoc so that there is no new document being created.

Second thing I did was playing around with the options on getByName into the actual sheet name. This was just for my specific document because I need specific names. The next was creating a loop.

This loop was a way to achieve a serialized number of copies, in the end my code look like this:

Sub CopySpreadsheet
  firstDoc = ThisComponent
  selectSheetByName(firstDoc, "Hoja1")
  dispatchURL(firstDoc,".uno:SelectAll")
  dispatchURL(firstDoc,".uno:Copy")
 for i = 1 To 14
  aNewSheetName = "Acceptance_Checklist_" & Format(i, "00")
  firstDoc.getSheets().insertNewByName(aNewSheetName,0)
  selectSheetByName(firstDoc, aNewSheetName)
  dispatchURL(firstDoc,".uno:Paste")
Next i
End Sub

I had to go several times into the book to get tips on how to build a loop. I even went to the OOoForum in order to get this question answered. Once it was answered, it was just logical processes. I got a loop on the aNewSheetName so that the label was Acceptance_Checklist and concatenated with the format(i, "00"), the i was the variable assigned by the For which continusly incremet the value of i until it reached 14.

The next line used the insertNew ByName function to generate that new sheet with the aNewSheetName label.

Finally we used the dispatchURL which use the UNO API to send the PASTE instruction.

The dispatcher is an interesting function because it let us send a command with less hassle. So instead of having to enumarte ALL the compoinents to get to the paste action we just need to call uno: PASTE. Same was done on Select and Copy early in that script.

Using a For enabled me to select a range, meaning that the sheet will be copied to a defined value which also protect the document from growing too big or crashing.

The script is not perfect however because the insertion takes place on a non-sequensial manner. That means the pages were inserted infront instead o the back of the list. This make me have a disjoined numeration such as:

109, 108, 107, 106.....4, 1, 2, 3

This clearly broke the order of the numeration, my guess is that this can be solved with a reverse loop.

on the next post I will talk about the label script to help me configure the content acording with the list of servers and how I got the datasource to connect with the script.