[Solved] Use X-Y Cell Coord in UnoService

[alf50]

Can I use X-Y Cell Coordinates rather than $A$11:$B$12 in the UnoService?
I would like to use:
I = 0
J = 10
K = 1
L = 11
args1(0).Value = (I,J):(K,L)

rather than

args1(0).Value = "$A$11:$B$12"

but I get an error when I try that.

Code: Select all

sub TxFntColor
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$X$11:$AJ$12"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Color"
args2(0).Value = 12910603
dispatcher.executeDispatch(document, ".uno:Color", "", 0, args2())
rem ----------------------------------------------------------------------
d

[FJCC]

I don't think you can do that but you can do

Code: Select all

oSheet = ThisComponent.CurrentController.ActiveSheet
oCellRng = oSheet.getCellrangeByPosition(0,10,1,11)
oCellRng.CharColor = 12910603

I can't test that code at the moment.

[alf50]

Thank you for the response. I figured out a simple way to convert the I,J coordinate pairs to TI,TJ coordinate pairs that will work with the dispatcher.

The issue I had was the =UNICODE($AL$2) Command would work in a spread sheet cell to read UNICODE Playing Card values that I copied and pasted into Cell $AK$2, but the command would not work in my macro, I had to insert it into a cell, in this case I used cell $AK$2. I could then read the UNICODE Number in $AK$2 into my macro, and change the color of the cell having the UNICODE Card character to Red for Heart Cards and Magenta for Diamond cards.

The code below works in my application

Code: Select all

sub ColrTst

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

Dim A as Double
Dim B as Double
Dim I,J,K,L,M,N as Integer
Dim Crd,TI,TJ,TK as String
dim document   as object
dim dispatcher as object
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
dim args3(0) as new com.sun.star.beans.PropertyValue
dim args4(0) as new com.sun.star.beans.PropertyValue
dim args5(0) as new com.sun.star.beans.PropertyValue
rem -------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem -------------------------------------------------
Doc = ThisComponent
Sheet = Doc.Sheets(0)
rem -----------------------------------------------
args1(0).Name = "ToPoint"
args1(0).Value = "$A$2"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem -----------------------------------------------

For J = 2 to 19
  For I = 1 to 19
	Cell = Sheet.getCellByPosition(I,J)
	Crd = Cell.string
	Cell = Sheet.getCellByPosition(37,1)
	Cell.string = Crd
	Cell = Sheet.getCellByPosition(36,1)
	A = Cell.value
	If (A > 127152 and A < 127167) Then 
		Cell = Sheet.getCellByPosition(I,J)
		TI = "$" & CHR(I+65)
		TJ = J + 1
		If J > 9 then TJ = Right(TJ,2)
		TK = TI & "$" & TJ
	rem	If I= 14 or I = 17 Then MsgBox "TI$TJ, TK = " & TI & "$" & TJ & "--" & TK
	rem	goto ColrT1:
		rem -----------------------------------------------
		args2(0).Name = "ToPoint"
		args2(0).Value = TK
		dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
		rem -----------------------------------------------
		args3(0).Name = "Color"
		args3(0).Value = 12910603
		dispatcher.executeDispatch(document, ".uno:Color", "", 0, args3())
	  Else
	End If

	If (A > 127168 and A < 127183) Then 
		Cell = Sheet.getCellByPosition(I,J)
		TI = "$" & CHR(I+65)
		TJ = J + 1
	rem	TJ = J
		If J > 9 then TJ = Right(TJ,2)
		TK = TI & "$" & TJ
	rem	If I= 14 or I = 17 Then MsgBox "TI$TJ, TK = " & TI & "$" & TJ & "--" & TK
	rem	goto ColrT1:
		rem -----------------------------------------------
		args2(0).Name = "ToPoint"
		args2(0).Value = TK
		dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
		rem -----------------------------------------------
		args3(0).Name = "Color"
		args3(0).Value = 12911603
		dispatcher.executeDispatch(document, ".uno:Color", "", 0, args3())
	  Else
	End If
  Next I
Next J
rem -----------------------------------------------
args4(0).Name = "ToPoint"
args4(0).Value = "$A$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem -----------------------------------------------
ColrT1:

end sub

The one issue that I have in this forum is that when examples are given, a working macro that includes all the Dimension statements, specifying what variables have to be Objects, Strings, Integers, Long Variables, Double Variables, etc are almost never provided. When you try to use an example to make something work, it almost never does.

Just some food for thought if we are trying to teach folks something about programing without frustrating them to the Nth degree.

[FJCC]

The one issue that I have in this forum is that when examples are given, a working macro that includes all the Dimension statements, specifying what variables have to be Objects, Strings, Integers, Long Variables, Double Variables, etc are almost never provided. When you try to use an example to make something work, it almost never does.

Just some food for thought if we are trying to teach folks something about programing without frustrating them to the Nth degree.

The DIM statements are only required if you have set Option Explicit.

[Zizi64]

The one issue that I have in this forum is that when examples are given, a working macro that includes all the Dimension statements, specifying what variables have to be Objects, Strings, Integers, Long Variables, Double Variables, etc are almost never provided. When you try to use an example to make something work, it almost never does.

Just some food for thought if we are trying to teach folks something about programing without frustrating them to the Nth degree.

Please upload YOUR sample file together with YOUR embedded full macro code. Without them we can give you partial tips only.

And never use the Dispatcher - if you want use the macros efficiently.

Maybe you can achieve this task witout any macro (to colorize cells based on the cell content): just use the Conditional formatting feature and the most valuable feature of the AOO/LO: the Styles.
Note: The User Interface of the LibreOffice can handle more than 3 conditions.