[Solved] Copy Specific Worksheet using Macro

[a70coupe]

Hello, it's your village idiot again.

I have recorded a macro to copy a specific worksheet called "Template" and all is working but the macro seems to be copying any sheet that is in position 2. I would like to know if there is a way to get the macro to copy the sheet specifically by it's name.

Here is what I have:

Code: Select all

sub NewNew
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 = "Nr"
args1(0).Value = 2

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

rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "DocName"
args2(0).Value = "Sales 2 - Open Office"
args2(1).Name = "Index"
args2(1).Value = 32767
args2(2).Name = "Copy"
args2(2).Value = true

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


end sub

[FJCC]

Here are two ways to do it. One is a modification of your code and the second does not use the recorded methods but rather the standard Application Programming Interface (API)

Code: Select all

Sub CopySheet1
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 ----------------------------------------------------------------------

TemplSheet = ThisComponent.Sheets.getByName("Template")
oCurrentController = ThisComponent.CurrentController
oCurrentController.setActiveSheet(TemplSheet)
rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "DocName"
args2(0).Value = "Untitled1"
args2(1).Name = "Index"
args2(1).Value = 32767
args2(2).Name = "Copy"
args2(2).Value = true

dispatcher.executeDispatch(document, ".uno:Move", "", 0, args2())
End Sub

Code: Select all

Sub CopySheet2
oSheets = ThisComponent.Sheets
oSheets.copyByName("Template", "TemplateCopy", oSheets.Count)
End Sub

[a70coupe]

Thank you FJCC! The 2nd option you provided is so much easier!