Setting Range or Cell borders using macro

[frozbie]

Hi,

Embarrising to admit but I'm stumped. I'm working on a macro that at various points needs to format cell borders. I have found documentation on Struct TableBorder and BorderLine:
http://api.openoffice.org/docs/common/r ... order.html
http://api.openoffice.org/docs/common/r ... rLine.html
and had a look through the developers manual and AndrewsMacro document, but I must be missing something as far as using these goes.

Can anyone advise if I'm on the right track or point me in the right direction? All I want to do is format a cell range with basic borders.

The following code is as far as I've been able to get, not very far.

Code: Select all

Sub setBorders
    Dim oDoc As Object 
    Dim oPlan As Object 
    Dim oExt As Object 
 	dim oProperties
    dim oBorder
	dim oBorderLine
    
   oDoc = ThisComponent 
   oPlan = oDoc.getSheets().getByName("Sheet1") 
   oExt = oPlan.getCellRangeByPosition(3,4,9,7) 

'// Tried modifying properties of TableBorder and BorderLine
'// as per following links
'http://api.openoffice.org/docs/common/ref/com/sun/star/table/TableBorder.html
'http://api.openoffice.org/docs/common/ref/com/sun/star/table/BorderLine.html

oBorder = oExt.TableBorder
oBorderLine = oBorder.TopLine
oBorderLine.Color = 4
oBorderLine.InnerLineWidth = 10
oBorder = oBorderLine
'oBorder.IsTopLineValid = True 'Throws error

'// Tried using setPropertyValue but always get an error
'oExt.setPropertyValue("TableBorder.InnerLineWidth", 0) '// Throws error
'oExt.setPropertyValue("TableBorder.TopBorder", 10) '// Throws error
'oExt.setPropertyValue("TopBorder", 10) '// throws error
'oBorder.TopLine.setPropertyValue("InnerLineWidth", 30) '// throws error

'// Following code does not crash but does not change object properties or spreadsheet!
oBorder = oExt.TableBorder
oBorder.IsTopLineValid = false '// however, this line does change IsTopLineValid property
oBorder.TopLine.Color = 7
oBorder.TopLine.InnerLineWidth = 0.5
oBorder.TopLine.OuterLineWidth = 1
oBorder.TopLine.LineDistance = 5

'// The following code changed values in the object
'// Checked using Watch but not on spreadsheet
oBorderLine = oBorder.TopLine
oBorderLine.InnerLineWidth = 100
oBorderLine.Color = 15
oBorderLine.OuterLineWidth = 100

End Sub

[frozbie]

Hi,

Okay, the following method works but surely there is another method. Can anyone shed light on the correct way to set TableBorders?

Mark

Code: Select all

Sub setBorders
    Dim oDoc As Object 
    Dim oPlan As Object 
    Dim oExt As Object 
	dim document   as object
	dim dispatcher as object

	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	
oDoc = ThisComponent 
oPlan = oDoc.getSheets().getByName("Sheet1") 
oExt = oPlan.getCellRangeByPosition(3,4,9,7) 

ThisComponent.CurrentController.Select(oExt) '// selected D5:J8!

'//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'// This method works only for cells that are selected!

dim args18(12) as new com.sun.star.beans.PropertyValue
args18(0).Name = "OuterBorder.LeftBorder"
args18(0).Value = Array(0,0,2,0)
args18(1).Name = "OuterBorder.LeftDistance"
args18(1).Value = 0
args18(2).Name = "OuterBorder.RightBorder"
args18(2).Value = Array(0,0,2,0)
args18(3).Name = "OuterBorder.RightDistance"
args18(3).Value = 0
args18(4).Name = "OuterBorder.TopBorder"
args18(4).Value = Array(0,0,2,0)
args18(5).Name = "OuterBorder.TopDistance"
args18(5).Value = 0
args18(6).Name = "OuterBorder.BottomBorder"
args18(6).Value = Array(0,0,2,0)
args18(7).Name = "OuterBorder.BottomDistance"
args18(7).Value = 0
args18(8).Name = "InnerBorder.Horizontal"
args18(8).Value = Array(0,0,2,0)
args18(9).Name = "InnerBorder.Vertical"
args18(9).Value = Array(0,0,2,0)
args18(10).Name = "InnerBorder.Flags"
args18(10).Value = 0
args18(11).Name = "InnerBorder.ValidFlags"
args18(11).Value = 127
args18(12).Name = "InnerBorder.DefaultDistance"
args18(12).Value = 0

dispatcher.executeDispatch(document, ".uno:SetBorderStyle", "", 0, args18())
'//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

End Sub