Skip to content

Instantly share code, notes, and snippets.

@vst
Created September 19, 2018 02:59
Show Gist options
  • Save vst/d78e40b4506aa36cbcec94d8a3433c5a to your computer and use it in GitHub Desktop.
Save vst/d78e40b4506aa36cbcec94d8a3433c5a to your computer and use it in GitHub Desktop.
Open/Libre Office Save All Sheets as CSV
' Run the SaveAllAsCSV subroutine.
' Creates a CSV file from the active sheet under the given directory.
'
' Based on https://forum.openoffice.org/en/forum/viewtopic.php?t=41284
sub SaveAsCSV(filename As String)
' Declare the document variable:
dim document as object
' Get the document:
document = ThisComponent
' Create a vector for "save" operation properties:
dim args(1) as new com.sun.star.beans.PropertyValue
' Define the filter name/value, ie. the file type to save as:
args(0).Name = "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
' Define the filter options specific to the CSV file type:
args(1).Name = "FilterOptions"
args(1).Value = "44,34,76,1"
' Save the file:
document.storeToURL(filename, args)
end sub
' Saves all non-hidden workbook sheets to CSV.
sub SaveAllAsCSV
' Load tools:
GlobalScope.BasicLibraries.loadLibrary("Tools")
' Get current directory:
initDir = DirectoryNameoutofPath(ThisComponent.getURL(), "/")
' Create a directory prompt:
dirPicker = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
dirPicker.setTitle("Choose a directory to save CSV file in")
dirPicker.setDisplayDirectory(initDir)
' Run the prompt:
if dirPicker.execute() then
directory = dirPicker.getDirectory()
else
Exit Sub
end if
' Get all sheets:
sheets = ThisComponent.Sheets.createEnumeration()
' Get the current controller:
controller = ThisComponent.getCurrentController()
' Iterate over sheets and save them if applicable:
while sheets.hasMoreElements()
' Get the sheet:
sheet = sheets.nextElement()
' Get the sheet name:
sheetName = sheet.getName()
' Check if the seet is visible:
if sheet.IsVisible then
' Activate the sheet:
controller.setActiveSheet(sheet)
' Save the sheet as CSV:
SaveAsCSV directory & "/" & sheetName & ".csv"
endif
wend
end sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment