[solved] copyByName not working in external file

Dear Forumers,
I have trouble to understand how working on opened ods file works.
I want to save actual file as another, then open it as hidden and copy sheet inside this file.
The problem is that component method copyByName doesn’t work due to “No method found”.
Other methods look working.
When I am using ThisComponent.copyByName - it is working.

Test code to show a problem below. Thanks!

Sub Main

	oView = ThisComponent.getCurrentController()
	oSheets = ThisComponent.getSheets()
	oSheet = ThisComponent.Sheets.GetByName("names")

	sSaveToURL = DirectoryNameoutofPath(ThisComponent.getURL(),"/") & "/test.ods"
	thisComponent.storeToUrl(sSaveToURL, Array())

	' output document file parameters
	Dim Props(0) As New com.sun.star.beans.PropertyValue
	Props(0).Name  = "Hidden"
	Props(0).Value = True

	' load output document file
	oDest = StarDesktop.loadComponentFromURL(sSaveToURL, "_blank", 0, Props()) ' "default", 0, args())
	oPattern1 = oDest.Sheets.GetByName("pattern_1")

	oDest.copyByName(oPattern1.Name, "New name sheet", oDest.Sheets.Count) 'ERROR HERE


End sub


You goal is not clear. It appears you are making a copy of the Calc file, then opening it ‘hidden’ and copying one sheet in that file to be a new sheet in that file.

You have a few items which are incorrect with the statement.

There is no method for copyByName using This Component. The method is located under Sheets. Therefore either of these lines will access the method:

Rem OR for the line you have

Now even with that fix, there is no name for the destination sheet → employeeName.String is not in your code.

Also you never save the modification (based upon the supposed goal) in the hidden file. You can do this with:

odocument = oDest.CurrentController.Frame
odispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
odispatcher.executeDispatch(odocument, ".uno:Save", "", 0, Array())

And when finished close the hidden document:


Here is the API reference → XSpreadsheets Interface Reference

Here is an answer which copies a sheet in the same document → Macro to Copy Current Sheet and insert it after current sheet

Thank you, you solved all problems. It is proof that working 8 hours per day is too long. Six hours are maximum :slight_smile:
I corrected my sample code about missing variable name and missing “Sheets” property.

For the save, I used these methods:


They looks more “clear” IMO.