Apache OpenOffice Community Forum

Posted: **Thu Aug 10, 2017 9:12 pm**

I click on cell A1 to activate it then record a macro to write ABC into cell A1.
How do I move the cursor by the macro to another cell and activate it?
No problem to activate another cell outside the macro and run the macro.
Det

Posted: **Thu Aug 10, 2017 9:30 pm**

Not needed to activate a cell if you want to change the cell content by a macro.

You can GET the document, the sheet, and the cells by the osage of the API (Application Programming Inteface) functions.

You can get a cell/cellrange by its coordinates,

Code: Select all

oCell = oSheet.getCellByPosition(0,0)

or by name:

Code: Select all

oCell = oSheet.getCellRangeByName("A2:C5")

viewtopic.php?f=5&t=7915
https://wiki.openoffice.org/wiki/Docume ... s_directly
https://wiki.openoffice.org/wiki/Docume ... and_Ranges

Then you can set the cell content:
oCell.String = "test"
or
oCell.Value = 100
or
oCell.Formula = "=A1+A2"

Posted: **Thu Aug 10, 2017 10:30 pm**

Let me clarify: I recorded just a simple action macro as a test.
sub Main
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 = "$A$132"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
end sub
How do I repeat the action in the following Cell A133?
Without going to the spreadsheet, click the next cell and run the macro again?

Posted: **Thu Aug 10, 2017 11:05 pm**

Sorry I missed some of the code, here it is again.
sub Main
rem define variables
dim document as object
dim dispatcher as object
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$132"

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

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "ABC"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())
end sub

Posted: **Fri Aug 11, 2017 7:46 am**

How do I repeat the action in the following Cell A133?
Without going to the spreadsheet, click the next cell and run the macro again?

Did you heard anything about the programming loops? Just organize your macro code into a loop. (For...Next or Do... While or Repeat...Until as you want. See the HELP of the Basic IDE)

(((Or: copy/paste the recorded macros two or more times below the recorded lines...

)))

I recorded just a simple action macro as a test.

You need WRITE your macros instead of recording, if you want work efficiently with the macros. The macro recorder can not organize the recorded code, and the macro recordfer can not record all of activity of the user, and the macro recorder can not work in every applications.
You need study the API description, the description of the Basic language and the Basic IDE.

Studying Andrew Pitonyak's free macro programming books - will be a good startpoint for you.

Posted: **Fri Aug 11, 2017 11:20 am**

This kind of recorded macro is a wondeful example for perfection: Perfect uselessness.

How do I repeat the action in the following Cell A133?
Without going to the spreadsheet, click the next cell and run the macro again?

Did you read the recorded code? If so you will have noticed that the cell address is recorded as a text constant. Therefore: If you go anywhere in the same sheet and run the "macro" it will always act on the inevitably same cell.

If you want to repeat a (most likely modified in steps) action for a range of adjacent cells you have to do as Zizi64 told you.

1. Forget about recording macros. (In rare cases for completely different tasks there may sometimes be some sense in macro recording.)
2. Learn how to get a cell range.
2.a Get the spreadsheet document.
2.b Get the single sheet you need to work in.
2.c Get the cell range as a part of the sheet.
3. Loop through the cells of the range and perform the respective actions.

The "active cell" seems to be an important player in MS Excel VBA code. It is not in Calc. In fact it is next to impossible to get the "active" cell if the term is meant to mean the cell currently having the input focus. Calc knows the active sheet and the current selection. Again: The Api does not "know" (give access to) the cell under focus, and this cell is not necessarily part of the current selection.

Let's assume you just clicked on a single cell's area. In this special case the current selection is a cell range consisting of a single cell. You can now run the following BASIC code using the api (not the dispatch helper):

Code: Select all

Sub FillABC3wide5high()
myDoc   = ThisComponent
mySel   = myDoc.CurrentSelection
mySheet = mySel.Spreadsheet
REM Now wanting to work on a range starting with the selected cell
REM or with the topmost leftmost cell of the selected range.
REM Suppose 3 columns wide and five rows high the cells shall get the text content "ABC".
myRange = mySheet.GetCellRangeByPosition(mySel.RangeAddress.StartColumn, _
                                         mySel.RangeAddress.StartRow, _
                                         mySel.RangeAddress.StartColumn + 3-1, _
                                         mySel.RangeAddress.StartRow    + 5-1)
For m = 0 To 5-1
    For n = 0 To 3-1
        oneCell = myRange.GetCellByPosition(n, m)
        oneCell.String = "ABC"
    Next n
Next m
REM The selection is still unchanged, and we didn't need to access an "active cell.
REM In fact we could do this very well without creating the myRange object. 
End Sub

I tested this for 1000 columns instead of three and 5000 rows instead of 5. I had to interrupt the Sub because a thorough estimate told me it would run about 1 h 10 min.
Looping through cells creating each one as an object is slow!
Is there remedy?
I made a similar Sub creating a copy of the DataArray of the complete range, filling it as needed, and writing it back to the range in one go:

Code: Select all

Sub FillWithText() ' For actual use: (ByVal pText As String, ByVal pWidth As Long, ByVal pHeight As Long)
REM Only for the test:
pText = "ABC?" : pWidth = 1000 : pHeight = 5000

myDoc   = ThisComponent
mySel   = myDoc.CurrentSelection
mySheet = mySel.Spreadsheet
myRange = mySheet.GetCellRangeByPosition(mySel.RangeAddress.StartColumn, _
                                         mySel.RangeAddress.StartRow, _
                                         mySel.RangeAddress.StartColumn + pWidth-1, _
                                         mySel.RangeAddress.StartRow    + pHeight-1)
myDataArray = myRange.GetDataArray
For m = 0 To pHeight-1
    For n = 0 To pWidth-1
        myDataArray(m)(n) = pText
    Next n
Next m
myRange.SetDataArray(myDataArray)
End Sub

I didn't need to interrupt this Sub. It completed the task in 49 s. Speed factor about 86. OK, it used an additional amount of RAM, of course.
It pays to consider efficiency.

Posted: **Sat Aug 12, 2017 3:40 pm**

Thank you all. As a newcomer to macros I just recorded one and tried to change it. The terminology is overwhelming. Every word in it is news to me. Here I found the syntax with examples I can understand.
https://wiki.openoffice.org/wiki/Docume ... _Documents
Now I can program by myself and forget the recorder. Thanks again ZiZi and Lupp
Det

Apache OpenOffice Community Forum

[Solved] Change the active cell

[Solved] Change the active cell

Re: Change the active cell

Re: Change the active cell

Re: Change the active cell

Re: Change the active cell

Re: Change the active cell

Re: Change the active cell