Why doesn't the macro change the color and borders of the cells, although it changes the text in cells?

The macro below is intended to put some text into cells, change backcolor and borders of these cells. It works properly when macro creates a new file and modifies its cells. But when the macro loads an existing file and modifies it, changing the text into cells works properly, while changing backgroundcolor and borders fails. Why? Windows 10, LO

Sub Borders
Dim oSheets
Dim oSheet
Dim i As Integer
Dim oDoc
Dim noArgs()
Dim URL As String

REM ------------------------------------
’ create new file
’ URL = “private:factory/scalc”
’ oDoc = StarDesktop.LoadComponentFromUrl(URL, “_default”, 0, noArgs())
’ OR
’ change existing file
Url = “file:///C:\ddd\File1.ods”
oDoc = StarDesktop.loadComponentFromURL(Url, “_default”, 0, noArgs())

oSheets = oDoc.Sheets
oSheet = oSheets.GetByName(“Sheet1”)
dim oBorder as object, oCell as object
oBorder = CreateUnoStruct(“com.sun.star.table.BorderLine”)
oBorder.Color = RGB(0, 0, 0)
oBorder.OuterLineWidth = 20
oSheet.GetCellByPosition(1, 1).string = “Valeur Total”
oSheet.GetCellByPosition(4, 1).string = Format(35.36, “0.00”)
oSheet.GetCellByPosition(4, 1).CellBackColor = RGB(0,0,100)
oSheet.GetCellByPosition(5, 1).string = “Euro”
oSheet.GetCellByPosition(5, 1).CellBackColor = RGB(100,0,0)
for i = 0 to 6
oCell = oSheet.getCellByPosition(i, 1)
oCell.TopBorder = oBorder
oCell.BottomBorder = oBorder
next i
msgbox “STOP”
End Sub


Had no problem with a newer LO version:

Version: (x64) / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Note: Your posted code has incorrect quotes and apostrophes.

Also functional in Libre Win10x64. Maybe try to uprage to the newer version.
Faster macro (with the method getCellRangeByPosition) is here.

Sub calcCellsBorderColor
	dim oDoc as object, oSheets as object, oSheet as object, sUrl$, oRange as object, oBorder as new com.sun.star.table.BorderLine
	oDoc=StarDesktop.LoadComponentFromUrl(sURL, "_default", 0, array())
	with oBorder
		.Color=RGB(200, 0, 150)
	end with
	oSheet.GetCellByPosition(1, 1).string="Valeur Total"
	oSheet.GetCellByPosition(4, 1).string=Format(35.36, "0.00")
	oSheet.GetCellByPosition(4, 1).CellBackColor=RGB(0,0,100)
	oSheet.GetCellByPosition(5, 1).string="Euro"
	oSheet.GetCellByPosition(5, 1).CellBackColor=RGB(100,0,0)
	rem apply properties to the Range is faster than cell by cell
	oRange=oSheet.getCellRangeByPosition(0, 1, 6, 1)
End Sub
1 Like

Upgraded LO to Nothing changed. When macro opens an existing file there is message “The lock file could not be created for exclusive access by LibreOffice, due to missing permission to create a lock file on that file location or lack of free disk space.” and file opens as “Read Only”. Nevertheless macro populates cells with text but can’t change backgroundcolor and borders.

@shma_lo1 you do not mention if your LO is 32 or 64 bit. My test is with 64.

Also may want to try resetting user profile - LibreOffice user profile

As for the lock file see: