[Solved] Loop in a macro: changing Calc's Active Cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
trvalentine
Posts: 9
Joined: Wed May 14, 2008 6:34 pm

[Solved] Loop in a macro: changing Calc's Active Cell

Post by trvalentine »

've greatly simplified what I'm actually trying to accomplish in the loop, but the following demonstrates what I'm doing.

Code: Select all

sub testing
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSelectedCells = ThisComponent.CurrentSelection
oActiveCell = oSelectedCells.CellAddress
nRow = oActiveCell.Row


Do Until nRow > 500

redim preserve step1(1) as new com.sun.star.beans.PropertyValue
step1(0).Name = "By"
step1(0).Value = 1
step1(1).Name = "Sel"
step1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, step1())
MsgBox "Next Item Found --- " & nRow

Loop

dim steplast(0) as new com.sun.star.beans.PropertyValue
steplast(0).Name = "ToPoint"
steplast(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, steplast())
MsgBox "steplast --- " & nRow

end sub 
The problem is that nRow will not change -- this results in an endless loop.
Can the Active Cell be changed within the loop so the value of nRow actually changes and the condition can be met?

TIA

T. R. Valentine
Last edited by Hagar Delest on Tue Jun 10, 2008 2:04 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
Ver 2,4 / Windoze (XP, Vista); Linux (Ubuntu) // wannabe macro programmer / Calc (mostly) & Writer
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Running a loop in a macro: changing Calc's Active Cell

Post by squenson »

Warning: Running the macro above as it is will force you to kill the process soffice.exe and soffice.bin and therefore changes in open documents may be lost. The reason is that the loop is endless and contains a pop-up box statement that prevents you to stop the macro by any other way!

Your problem is that you do not calculate again the value of the row of the active cell. Here is a working version of the macro (safe, the msgbox statement inside the loop has been commented out) where I have addedd three lines of code before the "loop" statement:

Code: Select all

sub testing
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSelectedCells = ThisComponent.CurrentSelection
oActiveCell = oSelectedCells.CellAddress
nRow = oActiveCell.Row


Do Until nRow > 500

redim preserve step1(1) as new com.sun.star.beans.PropertyValue
step1(0).Name = "By"
step1(0).Value = 1
step1(1).Name = "Sel"
step1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, step1())
' MsgBox "Next Item Found --- " & nRow

' Let's retrieve the position of the current cell
oSelectedCells = ThisComponent.CurrentSelection
oActiveCell = oSelectedCells.CellAddress
nRow = oActiveCell.Row

Loop

dim steplast(0) as new com.sun.star.beans.PropertyValue
steplast(0).Name = "ToPoint"
steplast(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, steplast())
MsgBox "steplast --- " & nRow

end sub
LibreOffice 4.2.3.3. on Ubuntu 14.04
trvalentine
Posts: 9
Joined: Wed May 14, 2008 6:34 pm

Re: Running a loop in a macro: changing Calc's Active Cell

Post by trvalentine »

squenson wrote:Warning: Running the macro above as it is will force you to kill the process soffice.exe and soffice.bin and therefore changes in open documents may be lost. The reason is that the loop is endless and contains a pop-up box statement that prevents you to stop the macro by any other way!
In my question, I noted that the macro, as written, 'results in an endless loop.' (But it can be killed without killing soffice.exe and soffice.bin -- hit the stop macro button in the OpenOffice Basic window).
squenson wrote:Your problem is that you do not calculate again the value of the row of the active cell.
Thanks. Now that it is pointed it out, it looks obvious. But I sure couldn't see it before. :oops:

Thanks again.

TRValentine
Ver 2,4 / Windoze (XP, Vista); Linux (Ubuntu) // wannabe macro programmer / Calc (mostly) & Writer
Post Reply