Automating work with Basic II
Wednesday, 4. April 2007, 01:47:37
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:
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:
Of 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:
So lets have an overview of the script:
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 oCell Dim oSheet REM Get the first sheet. oSheet = ThisComponent.getSheets().getByIndex(0) REM Get cell A2 oCell = oSheet.GetCellbyPosition( 0, 1 ) REM Move the selection to cell A2 ThisComponent.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 = ThisComponent
for x = 0 to 14
y= x
oSheet= firstDoc.getSheets().getByIndex(y)
REM GetCellbyPosition will indicate the cell of the title
REM SetString will insert the text into the cell
oCell=oSheet.GetCellbyPosition( 1,2 )
firstDoc.CurrentController.Select(oCell)
oCell.SetString(title(x))
REM for additional modification include the following:
REM Position of the cell -> GetCellPosition
REM Content of the cell -> SetString
oCell2=oSheet.GetCellbyPosition( 0,1 )
firstDoc.CurrentController.Select(oCell2)
oCell2.SetString("Account 1")
Next x
Of 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 *****
REM
REM Script to insert sequencially the list of servers
REM to execute you need to select the starting page +1
REM and then select the ending page on the 'for Loop'
REM
'Load all the variables including the array
Sub LabelSheet
Dim oCell as Variant
Dim firstDoc as Variant
Dim oSheet as Variant
Dim title() as Variant
Dim titulo()
Dim x
REM title array takes the list from a CSV dump
title=Array("otherhost1",_
"otherhost2",_
"otherhost3",_
"otherhost4",_
"otherhost5",_
"otherhost6")
REM x works as a range, it start from the begining of the ARRAY
REM y is used to put the final starting page adding the value of
REM the "first page"
firstDoc = ThisComponent
for x = 0 to 6
y= x
oSheet= firstDoc.getSheets().getByIndex(y)
REM GetCellbyPosition will indicate the cell of the title
REM SetString will insert the text into the cell
oCell=oSheet.GetCellbyPosition( 1,2 )
firstDoc.CurrentController.Select(oCell)
oCell.SetString(title(x))
REM for additional modification include the following:
REM Position of the cell -> GetCellPosition
REM Content of the cell -> SetString
oCell2=oSheet.GetCellbyPosition( 0,1 )
firstDoc.CurrentController.Select(oCell2)
oCell2.SetString("Server")
Next x
End Sub
So 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
WIDGETIZE