Calc macro: loop until only one sheet is left

[lgpa]

Hello all,

I have a macro I previously created to copy multiple sheets’ data into one, and would like to have it loop, but can’t figure it out. Hopefully you can help.

The basic rundown of the macro is this:

• There is always a sheet named Sheet1 at the beginning of the document.
• There are several following sheets with data.
• The macro selects the data from the sheet I am in, cuts it, pastes it into Sheet1, deletes the original sheet, and moves on to the next.

Currently, I am having to run the macro for every sheet until I am down to Sheet1. I want the macro to loop through them automatically until the only sheet remaining is Sheet1 and the rest have been copied/deleted.

Here is the current macro:

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


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 = "Sel"
args1(0).Value = false

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel"
args2(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$'Sheet1'.$A$1"

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

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Sel"
args5(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Sel"
args7(0).Value = false

dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args7())

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

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

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


end sub

Any help would be appreciated. Thanks!

[SM_Riga]

Hello @lgpa

To loop through sheets, you just need to know how many loops to perform. Count property of ThisComponent.Sheets object returns total count of sheets in your document. You can use this number to define how many loops shall be executed:

iSheetsCount = ThisComponent.Sheets.Count ' Get total sheets count
For i=1 to iSheetsCount-1 ' how many times loop shall be executed
' your code here
Next i

Your whole routine can look like this:

Sub CopyDeleteSheets
dim document   as object
dim dispatcher as object
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 = "Sel"
args1(0).Value = false
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel"
args2(0).Value = true
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$'Sheet1'.$A$1"
rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Sel"
args5(0).Value = false
rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false
rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Sel"
args7(0).Value = false

iSheetsCount = ThisComponent.Sheets.Count

For i=1 to iSheetsCount-1
ThisComponent.CurrentController.setActiveSheet(ThisComponent.Sheets(1))
dispatcher.executeDispatch(document, ".uno:GoToStart", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args5())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:GoToStartOfRow", "", 0, args7())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:JumpToNextTable", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Remove", "", 0, Array())
Next i

End Sub

Anyway, while you can use macro recorder for some simple tasks, it’s functionality is limited and if you expect some more complicated tasks in the future, you should consider learning common programming approach.

[lgpa]

Thank you so much! This works perfectly.

You’re totally right about learning more programming. I’ve been working on it as I need it and have time, but this one I needed done fairly quickly and my typical approach hadn’t gotten me there fast enough. Macro recorder works as a quick hack-together method, but as you said, it can’t handle some of the more complex features I’m starting to use.

Again, really appreciate your help.

[SM_Riga]

Good luck with learning then! And feel free to ask if any support needed, the community here will be glad to help!