/media/bill/HOWELL_BASE/System_maintenance/LibreOffice/macro_Howell_INNS_mass_email_macLib.txt
www.BillHowell.ca  ?dates? initial & updates
LibreCalc macro for deriving country source



REM  *****  BASIC  *****
' www.BillHowell.ca  11Feb2015
' This is to do several things : 
' 1. remove spaces in [Target Emails, Requested changes [additions, deletions], Undeliverables]
' 2. launch recalc


Sub cleanup()

process_rangeRows("Undeliverable","Undeliverable")
process_rangeRows("TargetEmails","Target_emails")
process_rangeRows("Requested changes","Add")
process_rangeRows("Requested changes","Delete")
'recalc_INNS_mass_emails()

End Sub


'*************************************************
Sub recalc_INNS_mass_emails()

Dim oDocs, oDoc, oSheets, oSheet, oCells, oCell, oRange, URL As Object
Dim Args(1) As new com.sun.star.beans.PropertyValue

dim document   as object
dim dispatcher as object

oSheet = ThisComponent.Sheets.getByName("Requested changes")

oCell  = oSheet.getCellRangeByName("recalc_start") 
oCell.value = now

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())


'recalculate()

oCell  = oSheet.getCellRangeByName("recalc_end") 
oCell.value = now


End Sub


'*************************************************
Sub process_rangeRows(sheetName As String, rangeName As String)

Dim oDocs, oDoc, oSheets, oSheet, oCells, oCell, oRange, arg, arg0 As Object
Dim cString,tString, tText As String
Dim jRow, col, row_start, row_ender As Integer
Dim tDate, tBlank, tNumber As Integer

oDoc   = ThisComponent
oSheet = oDoc.Sheets.getByName(sheetName)

row_start  =  oSheet.GetCellRangeByName(rangeName).RangeAddress.StartRow + 1
row_ender  =  oSheet.GetCellRangeByName(rangeName).RangeAddress.EndRow - 1
col        =  oSheet.GetCellRangeByName(rangeName).RangeAddress.StartColumn

For jRow = row_start to row_ender
   oCell   = oSheet.getCellByPosition(col, jRow) 
   tText  = oCell.String
   IF isDate(tText)  THEN 
            tDate    = 1 
      ELSE  tDate    = 0  
      EndIf
   IF tText= ""  THEN 
            tBlank   = 1
      ELSE  tBlank   = 0 
      EndIf
   IF isNumeric(tText)  THEN 
            tNumber = 1 
      ELSE  tNumber = 0 
      EndIf
   tValue   = tDate + tBlank + tNumber 
   IF tValue < 1 THEN 
      remove_spaces(oCell) 
      EndIf
   Next jRow 

End Sub



'*************************************************
Sub remove_spaces(oCell As Object)

Dim oDocs, oDoc, oSheets, oSheet, oCells, oRange As Object
Dim jRow, col, charStart, charEnder As Integer
Dim oString, aString As String

'aString = oCell.string
'Trim ONLY removes leading and trailing spaces!!
oCell.string = Trim(oCell.string)

End Sub 




'enddoc









enddoc