Copy a sheet to an unopened to workbook

Creating a macro - Writing a Script - Using the API

Copy a sheet to an unopened to workbook

Postby Fraz627 » Tue Apr 17, 2018 3:20 am

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
Fraz627
 
Posts: 22
Joined: Sat Jun 17, 2017 8:31 am

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

Postby Zizi64 » Tue Apr 17, 2018 6:58 am

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
https://forum.openoffice.org/en/forum/v ... hp?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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
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
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy a sheet to an unopened to workbook

Postby Sébastien C » Tue May 01, 2018 6:03 pm

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   Expand viewCollapse view
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 41 times
Last edited by Sébastien C on Tue May 01, 2018 7:53 pm, edited 1 time in total.
LibreOffice 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Postby Villeroy » Tue May 01, 2018 7:09 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy a sheet to an unopened to workbook

Postby Sébastien C » Tue May 01, 2018 7:39 pm

@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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Postby Villeroy » Tue May 01, 2018 8:20 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy a sheet to an unopened to workbook

Postby Sébastien C » Tue May 01, 2018 9:45 pm

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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Copy a sheet to an unopened to workbook

Postby Villeroy » Wed May 02, 2018 1:58 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests