Created
April 8, 2015 06:34
-
-
Save geminorum/7c6405dec40c41ee9500 to your computer and use it in GitHub Desktop.
LibreOffice Macro to save Excel Sheets in separate UTF CSV files. modified from http://www.jpstacey.info/blog/2014-09-12/exporting-all-libreoffice-worksheets-csv
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ExportAllToCsv | |
document = ThisComponent | |
' Use the global string tools library to generate a base filename for each CSV | |
' based on the current prefixless filename | |
GlobalScope.BasicLibraries.loadLibrary("Tools") | |
BaseFilename = Tools.Strings.GetFileNameWithoutExtension(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 = "44,34,76,1" | |
'Propval(1).Value = "59,34,0,1,1" 'ASCII 59 = ; 34 = " | |
'Propval(1).Value = "UTF8, CRLF ,,," | |
'Propval(1).Value ="59/9,34,UTF_8,1,1/1/1/1/1/1/1/1" 'string mit den Optionen | |
'44 = field delimiter "," | |
'34 = text delimiter, Chr(34) = " | |
'76 = UTF8 | |
For I = 0 to NumSheets | |
' For each sheet, assemble a filename and save using the filter | |
SheetName = Sheets(I).Name | |
document.getCurrentController.setActiveSheet(Sheets(I)) | |
Filename = "e:\tmp\" + BaseFilename + "." + SheetName + ".csv" | |
FileURL = convertToURL(Filename) | |
document.StoreAsURL(FileURL, Propval()) | |
Next I | |
' Libreoffice thinks our filename is now the last-exported-CSV filename, so close. | |
Msgbox "Files saved as /tmp/" + BaseFilename + ".*.csv. You'll need to close the spreadsheet now." | |
End Sub | |
' https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=41284#p190106 | |
sub StoreCSV(sURL As String) | |
dim document as object | |
document = ThisComponent | |
dim args(1) as new com.sun.star.beans.PropertyValue | |
args(0).Name = "FilterName" | |
args(0).Value = "Text - txt - csv (StarCalc)" | |
args(1).Name = "FilterOptions" | |
args(1).Value = "44,34,76,1" | |
document.storeToURL(sURL,args) | |
end sub | |
Sub Sheets_To_CSV | |
sheets=ThisComponent.Sheets.createEnumeration() | |
sURL = ThisComponent.getURL() | |
oView = ThisComponent.getCurrentController() | |
while sheets.hasMoreElements() | |
sh=sheets.nextElement() | |
s=sh.getName() | |
if sh.IsVisible then | |
oView.setActiveSheet(sh) | |
StoreCSV sURL &"_"& s &".csv" | |
endif | |
wend | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks. It helps me!
My LibreOffice ver. is 5.1.5.2
But it break when running at line 59:
I fixed as
Then it works.