Automating work with Basic II

So this is the second part of my blog where I explain the logic used and how I got my script to run in OpenOffice.org.The first thing I did was take a look and search on Andrew Pitonyak Macro book, to find a code that will come close to do what I have in mind.The code is simple, this time I found a snippet that was able to select a cell. The original code was the following: Dim oCellDim oSheetREM Get the first sheet.oSheet = ThisComponent.getSheets().getByIndex(0)REM Get cell A2oCell = oSheet.GetCellbyPosition( 0, 1 )REM Move the selection to cell A2ThisComponent.CurrentController.Select(oCell)We can see in this snippet that the process is simple, simply get the sheet from the document and position the cursor on the sheet. Finally call on the CurrentController and use the native function of select.Here we can see how this script go and how will it fit with our original design. My script needed to switch pages and go incrementally through the ammount of sheets in the spreadsheet.What I did was obvious, I did a loop, my only concern was, what kind of loop. So far I found the for…next however there are the Do while, and Do Loop. I also needed a range since the loop can’t go forever, and finally a way where it can get the data for the population of such things like the title.I was going in a way to recreate the mail merge for spreads, the data however would be store in an array. The book also came handy at this point since I didn’t exactly how to create arrays in OOoBasic.The samples showed me the path and I was able to make a script that will look like this: firstDoc = ThisComponentfor x = 0 to 14y= xoSheet= firstDoc.getSheets().getByIndex(y)REM GetCellbyPosition will indicate the cell of the titleREM SetString will insert the text into the celloCell=oSheet.GetCellbyPosition( 1,2 )firstDoc.CurrentController.Select(oCell)oCell.SetString(title(x))REM for additional modification include the following:REM Position of the cell -> GetCellPositionREM Content of the cell -> SetStringoCell2=oSheet.GetCellbyPosition( 0,1 )firstDoc.CurrentController.Select(oCell2)oCell2.SetString(“Account 1”)Next xOf course that was not all their script and I also need to expand the script to not just label the first array but also static cell content that will be reapeated without major changes. In my script, this was the case with the title of the account, this required no datasource as the content should stay in place.I also didn’t show here the datasource but you can see how the datasource is handled, the array ‘title’ was able to have a counter instead of their values so the loop registration worked sequensially.This script is extremely simple since you dont really need any UNO components and is basic loops will go through the created sheets, position on their cells and finally populate it with the infrmation of the array.In the end the script looked like this:REM ***** BASIC *****REMREM Script to insert sequencially the list of serversREM to execute you need to select the starting page +1REM and then select the ending page on the 'for Loop'REM‘Load all the variables including the arraySub LabelSheetDim oCell as VariantDim firstDoc as VariantDim oSheet as VariantDim title() as VariantDim titulo()Dim xREM title array takes the list from a CSV dumptitle=Array(“otherhost1″,_”otherhost2″,_”otherhost3″,_”otherhost4″,_”otherhost5″,_”otherhost6”)REM x works as a range, it start from the begining of the ARRAYREM y is used to put the final starting page adding the value ofREM the “first page”firstDoc = ThisComponentfor x = 0 to 6y= xoSheet= firstDoc.getSheets().getByIndex(y)REM GetCellbyPosition will indicate the cell of the titleREM SetString will insert the text into the celloCell=oSheet.GetCellbyPosition( 1,2 )firstDoc.CurrentController.Select(oCell)oCell.SetString(title(x))REM for additional modification include the following:REM Position of the cell -> GetCellPositionREM Content of the cell -> SetStringoCell2=oSheet.GetCellbyPosition( 0,1 )firstDoc.CurrentController.Select(oCell2)oCell2.SetString(“Server”)Next xEnd SubSo lets have an overview of the script:

  • Select a variable
  • Populate the array
  • get the document
  • for loop with the start of the number of array data
  • y variable was put in case you need to start in a document other than 0
  • oSheet selects the sheet in the document
  • oCell gets in the cell
  • Select the cell
  • Insert value with set string from the Array
  • Repeat the process but this time with static as opposed to the loopy title array
  • finish the sub
Advertisements