Copy a sheet to an unopened to workbook

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Fraz627
Posts: 22
Joined: Sat Jun 17, 2017 8:31 am

Copy a sheet to an unopened to workbook

Post by Fraz627 »

How would one go about copying a sheet to an unopened workbook.

The source workbook has many sheets but only a few need to be distributed, and would like a macro to accomplish this task.

Thanks
Open Office 4.1 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro to copy a sheet to an unopened workbook.

Post by Zizi64 »

How would one go about copying a sheet to an unopened workbook.
- You must study and use the API functions (LoadComponentFromURL; StoreToURL, StoreAsURL, etc...:
https://wiki.openoffice.org/wiki/Docume ... tarDesktop
 Edit: the broken, Hungarian locale link is fixed 
- You must load/open the ODF type target spreadsheet file by the macro - based on its URL

- You must get/insert the target Sheet and the target Cell/Range by the macro
viewtopic.php?t=20808

- You must copy/paste the content and/or the styles/formatting properties by the macro to the target place

- You must save/store the target spreadsheet file by the macro.
Last edited by Zizi64 on Tue May 01, 2018 7:43 pm, edited 4 times in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Post by Sébastien C »

Hello everyone,

The subject is almost evoqued recently on the french forum.
And I find it more efficient of just copy/paste because the goal asked by Fraz627 is to copy one or more entire sheet(s)...

Below is a code for exporting in a new file (C:\communication.ods) with just the sheets nammed "sheetName_02" and "sheetName_03"

Code: Select all

Sub copySheet()
	Dim      myDestFile As Object,       myDestSheet As Object
	Dim  mySheetsName() As String,  mySourceFileName As String,  myTargetFileName As String
	Dim               i As Integer
	Dim propFich()

	mySourceFileName = "C:\testBlabla.ods"
	myTargetFileName = "C:\communication.ods"

	    mySheetsName = array("sheetName_02", "sheetName_03")

	      myDestFile = starDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, propFich)

	For i = 0 to ubound(mySheetsName)
	 myDestFile.sheets.insertNewByName(mySheetsName(i), (i + 1))
	 myDestSheet = myDestFile.sheets.getByName(mySheetsName(i))

	 myDestSheet.link(convertToURL(mySourceFileName), mySheetsName(i), "", "", com.sun.star.sheet.SheetLinkMode.NORMAL)
	 myDestSheet.LinkMode = com.sun.star.sheet.SheetLinkMode.NONE
	 myDestSheet.LinkURL = ""
	Next i

	If fileExists(myTargetFileName) Then kill(myTargetFileName)

	myDestFile.sheets.removeByName(myDestFile.sheets.getByIndex(0).name)
	myDestFile.storeAsURL(convertToURL(myTargetFileName), propFich)
	myDestFile.close(true)

	msgBox("Copy OK", 64, "Done")
End Sub

Of course, the workbook has to be open for writing into. But I close it just after the saving.

I think it can help...
:)
Attachments
testBlabla.ods
An stupid example for testing.
(11 KiB) Downloaded 207 times
Last edited by Sébastien C on Tue May 01, 2018 7:53 pm, edited 1 time in total.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy a sheet to an unopened to workbook

Post by Villeroy »

Without macro:
1. Store data in a database (simple dBase instead of spreadsheet will do) and register it.
Re: Transfering data in Calc. to dbase
2. Copy sheet data to database through the data source window.
Hit F4 for the data source window.
Select the data in question and drag the selection onto the [Tables] section of your database.
In the following dialog, fill in the name of the database table, choose "Append data" and if the column order is the same in Calc and dBase, you can skip the dialog's next step.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Post by Sébastien C »

@Villeroy
My LibreOffice has a problem for read at Calc's workbook as a data-source. I don't know why but I search for a right test of your solution.
Do your copy process preserv layout, enrichment, especially, formulas ???
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy a sheet to an unopened to workbook

Post by Villeroy »

Base can read spreadsheets (ods, xls, whatever) but not write. This is why I recommend to save a database-like spreadsheet list as dBase and then connect Base to dBase. A dBase directory is the most simple writable data source.
And no, a database stores data. Data are strictly separated from processing which is a huge advantage over spreadsheets. Having your data in dBase, you can do simple calculations by means of queries and the more complex stuff can be calculated in a linked spreadsheet where the sheet is the target and the database is the source.
[Tutorial] Using registered datasources in Calc
Since OOo1.0 I used a simple dBase database with Calc's pivot tables for monthly aggregations of data without a single formula.

Having your data in a real relational database, you can do all calculations by means of queries.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Post by Sébastien C »

It is exactly what I thinked...

But there are cases where using a spreadsheet SIMPLY, with the basic features it offers, seems to me still much easier than mounting a gas plant, that would calculate elsewhere data or simply delete a formatting that, at failure to be significant, may be necessary for the proper presentation of a communication.

I do not know if this is said in English, but in French it says "the mountain gives birth to a mouse". :oops:

And where did you read that Fraz627 tells us he uses spreadsheets as a database ??? The question of Fraz627 was absolutely not brought to the rules of the Computer Art (which I have some notions myself if I can afford), but to take sheets of Calc (so likely to contain all that offers a spreadsheet) and copy them into a other workbook.

Let's wait for Fraz627's answer, which will tell us more about his real desire...
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy a sheet to an unopened to workbook

Post by Villeroy »

Spreadsheets are not simple. And when you want to "Copy a sheet to an unopened workbook" you are trying to collect data. You can not collect data in spreadsheets without running into heavy trouble.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply