This script is regarding my previous two post, Automating work with Basic I and Automating work with Basic II. However this script does much more, thanks to the friends at OOoForum.org. This scripts does the following:
- Now we have variables, and index arrays
- We have a looper that basically loop whatever you put on your document through all the pages
- Create a database connection succesfully and make a dump into a MSGBox
- ToDo: get the SQL Query into the document
So here is the first script that I modify and was using the uBound(array()) using a for loop I was able to generate new sheets on the calc file and label the page and finally paste the content. For more information look at the past posts.
With OpenOffice.org Calc now I got an easy way to generate new pages and duplicate the content however most of this content is not the same and even if the uBound() helped me automate the number of the array, is still a pain to have just 1 data string for generating documents. I mean, if I had to insert custom data beside this array I would need a multidimensional array, which is more sort of a hack. So now I guess is time to migrate to a real database and what best than using OpenOffie.org’s Base.So now I got a script that will do exactly that, connect to a database and execute a Query, this way I would be able to manipulate tables and database. However since Basic is so cool I can actually fill out the table from the same scripting and looping technique.So let me break it down first, the Database script consist on this one:
for i to uBound(array())aNewSheetName = "Acceptance_Checklist_" & Format(i, "00")firstDoc.getSheets().insertNewByName(aNewSheetName,0) 'insertByNewByName to label the sheetselectSheetByName(firstDoc, aNewSheetName) 'selectSheetByName re-insert the original namedispatchURL(firstDoc,".uno:Paste") 'paste the name as the new sheetNext iEnd Sub
- Start the function and validate the variables
- Then get the database service called DatabaseContext
- Use the getByName() function for my database
- Then we GOTTA use some authentication even if there is no user/password but we use it with the function getConnection(“”, “”)
- The most important part is creating the SQL statment using the method createStatement()
- Then it finally comes the meat to get the actual SQL. One note is to get double-double quotes for declaring tables
- Finally we get the to execute the statement using executeUpdate(), alternatively is executeQuery()
So here is the script:
oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oBaseContext.getbyName("TriadServers")
oCon = oDataSource.getConnection("", "")
Stmnt = oCon.createStatement()
strSQL = "Update ""readDox"" SET ""Architecture""='Horizon Clinicals Infrastructure Web Server' WHERE ""readDox"".""ID"" BETWEEN 49 AND 117"
Result = Stmnt.executeUpdate(strSQL) '
oCon.close()End SubThe next script I want to explain is the looper one that allow me to just type once in one cell and see the content replicate through my pages. The explanation is the following:
- Same as the rest is put the function and the variables
- We focus on the current document using the ThisComponent
- We use the awesome getCurrentSelection method
- Now we try to detect the cell doing a nice method to locate the row and columns using CellAddress.Column and CellAddress.Row
- After that we can get getString which save us the Select and Copy
- Then the loop will use the getByIndex() so it goes through the pages.
- Finally the script that makes it all is inserting the string back into the cell with SetString()
So here comes the script…
Sub CellLabeler Dim oCell as Variant Dim firstDoc as Variant Dim oSheet as Variant Dim someText as Variant Dim x firstDoc = ThisComponent oCell = firstDoc.getCurrentSelection column = oCell.CellAddress.Column row = oCell.CellAddress.Row someText = oCell.getString oSheets = firstDoc.getSheets for x = 0 to oSheets.Count -1 oSheet= firstDoc.getSheets().getByIndex(x) oSheet.GetCellbyPosition( column , row ).SetString(someText) Next xEnd Sub