How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells

JoeBachofen — Wed, 30 Oct 2024 00:07:13 GMT

Two "rem JB" and one code line describe my intent and my question in the VB code below:

REM ***** BASIC *****

sub SortA
rem JB Macro activated by pushbuttons in any 1 of 11 columns {C-L,M}
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 = "AreaSort"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, Array())

rem JB Set args3(6).Value to the column value of the cell with the push button JB
dim args3(9) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ByRows"
args3(0).Value = true
args3(1).Name = "HasHeader"
args3(1).Value = false
args3(2).Name = "CaseSensitive"
args3(2).Value = false
args3(3).Name = "NaturalSort"
args3(3).Value = false
args3(4).Name = "IncludeAttribs"
args3(4).Value = true
args3(5).Name = "UserDefIndex"
args3(5).Value = 0
args3(6).Name = "Col1"
args3(6).Value = 14
args3(7).Name = "Ascending1"
args3(7).Value = true
args3(8).Name = "IncludeComments"
args3(8).Value = false
args3(9).Name = "IncludeImages"
args3(9).Value = true

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args3())

end sub

REM End ***** BASIC *****

LibreOffice 24.2.6.2 (X86_64)

If you can help, thank you; if not, I will post my solution when I find it. 🙂

Re: How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells

Kurt_Bremser — Wed, 30 Oct 2024 09:20:02 GMT

And where is the connection with SAS software?

Re: How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells

JoeBachofen — Wed, 30 Oct 2024 17:52:52 GMT

My apologies, I had seen a similar but less detailed post on the SAS community site with no apparent connection to SAS sw so I assumed it was a reasonable question to ask. I am close an answer on my own but I do appreciate your reply.

Have fun today. 🙂

topic Re: How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells in SAS Programming

How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells

Re: How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells

Re: How do I use SpreadSheet column number as a dynamic variable in a Macro to Sort a range of cells