[Solved] Use X-Y Cell Coord in UnoService

Discuss the spreadsheet application

[Solved] Use X-Y Cell Coord in UnoService

Postby alf50 » Fri May 01, 2020 10:45 pm

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   Expand viewCollapse view
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
Last edited by MrProgrammer on Sun May 03, 2020 2:05 am, edited 2 times in total.
Reason: Added ✓ to [Solved] topic
OpenOffice 4.1.5 on Mac El Capitan(10.11.6) and MS Windows10
alf50
 
Posts: 102
Joined: Sun Jun 13, 2010 2:55 pm

Re: Can I use X-Y Cell Coordinates rather than $X$11 in the

Postby FJCC » Fri May 01, 2020 11:03 pm

I don't think you can do that but you can do
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.CurrentController.ActiveSheet
oCellRng = oSheet.getCellrangeByPosition(0,10,1,11)
oCellRng.CharColor = 12910603

I can't test that code at the moment.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7748
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Can I use X-Y Cell Coordinates rather than $X$11 in the

Postby alf50 » Sun May 03, 2020 12:40 am

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   Expand viewCollapse view
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.
OpenOffice 4.1.5 on Mac El Capitan(10.11.6) and MS Windows10
alf50
 
Posts: 102
Joined: Sun Jun 13, 2010 2:55 pm

Re: Can I use X-Y Cell Coordinates rather than $X$11 in the

Postby FJCC » Sun May 03, 2020 3:24 am

alf50 wrote: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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7748
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Use X-Y Cell Coord in UnoService

Postby Zizi64 » Sun May 03, 2020 6:56 am

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9540
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests