CALC 7.5.5.2 : Copy every sheet to a separate ODS file

English
,
#1

Hello,

I work on a big file containing up to 20 different sheets. To be consulted on a tablet, the file is too big and takes too long to open and the slow scrolling makes it unusable.

Therefore, I would like to find a way to copy each sheet in a separate ODS file having the name of the sheet.
I know it can be done manually with a right-click on tha sheet’s tab, but I’d like to be able to make it al at once every time I need to.

My big source file remains the reference on which I will make the modifications. The separate ODS files are only for consultation from my Google Drive on my tablet when roaming

Just to simpify the job, there are no references between cells of different sheets. So a plain simple copy sheet to new file is sufficient, but repeated for each sheet in the main file.

All help will be appreciated.

Please note I am totally newbie in programming scripts, macros… in L.O :blush:

Thanks a lot and have a nice day

#2

Tools>Options>Security>[Macro Security…]
Level: Highest
Trusted sources: some directory or directories in your home directory but NOT your download directory.

Save the attached document to a trusted directory and open it from there.
Click the install button.
Open your spreadsheet.
menu:Tools>Macros>Run… and run pyCalc>storeSpreadsheets>Main

The macro stores each sheet under its name with .ods suffix in the same directory as the source file.
storeSpreadsheets.odt (49.9 KB) (second verstion removing extra sheets)

1 Like
#3

Hi,

It works !

Only very small glitch is that in each of the resulting files, there is also an empty “Sheet 1”, but for the sake of me learning a bit of Python, I will try to find how to change that.

Thanks a lot !

#4

The blank extra sheet will not harm. It does not consume any memory. If the source document has it’s own “Sheet1”, Calc will rename the imported sheet as “Sheet1_1”. EDIT: both issues fixed in second version
“A bit of Python” will not do. You need to be at advanced hobbyist level before you start taming this beast of an API. Same with Basic and Java. Mastering the language is a precondition. The difficult part is the office API.

#5

Yes indeed… It was easier said than done :rofl:
I remember times when writing a macro in Excel was a piece of cake… but that was more than 20 years ago.

Thank you again very much for your effort !

I hope this will serve other LibreOffice users as well.

#6

Not to bash LibreOffice, but writing a macro in Excel is still as easy or easier than ever (minus manipulating charts, which changed for 2007 release). LibreOffice’s API is more difficult to tame than Excel’s, partly because of the API itself, and partly because of the integration into the macro IDE.

#7

The second version removes all trailing sheets and renames the remaining sheet back to the original name (e.g. Sheet1_1 → Sheet1).

1 Like
#8

Thanks Andreas: it works perfectly ! :+1: :+1: :+1:
For the record, and just for fun, I spent some time with ChatGPT trying to make the same thing, but never came to a satisfaying result. Most of the time, an exception was cast, and when it almost worked, the formatting of the cells was lost.

After some time, ChatGPT began to re-propose previous solutions already given, or totally changed the initial goal, for example by proposing to copy each sheet in a new file, but all sheets in the same file, resulting in a perfectly unefficient file copy… :rofl:

In the meantime, I installed numerous Python librariesthat I will probably never use anymore…
Nevermind…

@joshua4:
I do not understand how this could have been done… I think they are completely missing their target audience: small enterprises with virtually no IT service, individuals such as myself… It makes all the “macros” (including in this term all variations: scripts, modules, …) just plain unusable for 95 % of the users…
Anyway, this will not make me return to Excel…
There is some work here for the development teams to find a way to mitigate that empediment…

(Not bashing either, because overall, the dev’s made a good job with LO, just merely stating a fact :wink:)

Have a nice day

#9

ChatGPT has zero conceptual knowledge. It just concatenates words by mere probability. And it is a Microsoft product. Why should Microsoft improve it to write better LibreOffice macros? They wasted many millions of dollars to supress OpenOffice.org and LibreOffice.

Macro programming in LibreOffice is … well, it is about programming actually. The API is a very thin layer on top of the source code. It takes a few weeks until you get your head around it, taking some programming skills as given.
This whole office suite weighs 500 MB including three macro languages, various database drivers and unprecedented variety of file filters.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.