[Solved] Copy Specific Worksheet using Macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
a70coupe
Posts: 9
Joined: Thu Jul 19, 2012 10:59 pm

[Solved] Copy Specific Worksheet using Macro

Post by 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
Last edited by a70coupe on Tue Jul 24, 2012 1:03 am, edited 1 time in total.
LibreOffice 3.4.2 OOO340m1 (Build:203) on Windows Vista SP2
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Copy Specific Worksheet using Macro

Post by 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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
a70coupe
Posts: 9
Joined: Thu Jul 19, 2012 10:59 pm

Re: [Solved] Copy Specific Worksheet using Macro

Post by a70coupe »

Thank you FJCC! The 2nd option you provided is so much easier!
LibreOffice 3.4.2 OOO340m1 (Build:203) on Windows Vista SP2
Post Reply