I needed a way to export all the sheets out of a LibreOffice Calc spreadsheet into individual CSV files that could be imported into the game I was building. It had to be quick so this could be used as a fast iterative process. LibreOffice macro scripting accomplishes this.
I found that J-P Stacey had created a macro doing much the same thing I needed. The only downside was that it required you to reopen the document each time you exported. With some digging into the macro API, I found the solution to that and modified the macro so that it creates the CSV files and keeps the original file open.
In LibreOffice 5, macros aren't exactly well documented and the UI is rather confusing, so I will give full instructions to get this macro working.
In LibreOffice, go to Tools > Macros > Organize Macros > LibreOffice Basic…
Select where you want to save the macro, I used the default of Standard > Module1. Click the New button.
Now copy and paste the macro below into the macro window, replacing the empty function that was created.
REM ***** BASIC ***** Sub ExportToCsv document = ThisComponent ' Use the global string tools library to generate a path to save each CSV GlobalScope.BasicLibraries.loadLibrary("Tools") FileDirectory = Tools.Strings.DirectoryNameoutofPath(document.getURL(), "/") ' Work out number of sheets for looping over them later. Sheets = document.Sheets NumSheets = Sheets.Count - 1 ' Set up a propval object to store the filter properties Dim Propval(1) as New com.sun.star.beans.PropertyValue Propval(0).Name = "FilterName" Propval(0).Value = "Text - txt - csv (StarCalc)" Propval(1).Name = "FilterOptions" Propval(1).Value ="59,34,0,1,1" 'ASCII 59 = ; 34 = " For I = 0 to NumSheets ' For each sheet, assemble a filename and save using the filter document.getCurrentController.setActiveSheet(Sheets(I)) Filename = FileDirectory + "/" + Sheets(I).Name + ".csv" FileURL = convertToURL(Filename) document.StoreToURL(FileURL, Propval()) Next I End Sub
Close the macro window. Right click on a toolbar where you want to add a button to run the export macro and choose Customize Toolbar…
Click on Add Command. From the Category section, choose LibreOffice Macros > My Macros > Standard > Module1 (or wherever you saved the macro earlier). Select ExportToCsv from the Command section and click Add. Click Close to close the window.
ExportToCsv has now been added to your toolbar. You can place it anywhere you want in the toolbar with the up and down arrows.
Running this macro will place a CSV file for each sheet alongside the original document. It overwrites any file that was previously there, so be aware of any naming conflicts that could arise before running this macro.
Differences from the original macro
The biggest difference between this and the original J-P Stacey macro is the subtle change from
document.StoreAsURL() (equivalent to the Save As… command) to
document.StoreToURL() (equivalent to the Save a Copy… command). That one change means you don't have to reopen the document every time you export, which made this a perfect macro for fast iteration.