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.
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:Sub MigrateTable
Dim oDataSource
Dim x
Dim oBaseContext
Dim oResult
Dim strSQL
Dim Stmnt
Dim Stmnt2
Dim strSQL2
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) '
Next x
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
Ideas, thoughts and Free Software 