CODEWITHSUNDEEP
libreofficelibreoffice-calclibreoffice-basic
Ds1715
Ds1715

Reputation: 41

LibreOffice Calc macro to save a named sheet in a csv file

There is a Calc file named as 'Data.ods' with a sheet named 'DataSheet' which should be saved in a 'Data.csv' file in the current directory using {tab} as field delimiter and double quoted data fields. How to implement this in LO Basic macro?

Upvotes: 2

Views: 2727

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

You have not specified whether the macro should open the 'Data.ods' spreadsheet or whether it is already open. This code works with the current spreadsheet:

Sub Generate_CSV()
Dim sURL As String ' URL of current spreadsheet
Dim FileN As String ' URL of target CSV-file
Dim oCurrentController As Object ' Before save - activate sheet sSheetName
Dim storeParms(2) as new com.sun.star.beans.PropertyValue 
Const sSheetName = "DataSheet"
   GlobalScope.BasicLibraries.LoadLibrary("Tools") ' Only for GetFileName
   sURL = thisComponent.getURL()
   If Not thisComponent.getSheets().hasByName(sSheetName) Then
        MsgBox ("Sheet """ & sSheetName & """ Not Found In Current Spreadsheet")
        Exit Sub
   EndIf
   FileN = GetFileNameWithoutExtension(sURL) & ".csv" ' For Data.ods it will be Data.csv
REM Options to StoreTo:
   storeParms(0).Name = "FilterName"
   storeParms(0).Value = "Text - txt - csv (StarCalc)"
   storeParms(1).Name = "FilterOptions"
   storeParms(1).Value = "9,34,,65535,1,,0,true,true,true" 
REM About this string see https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
   storeParms(2).Name = "Overwrite"
   storeParms(2).Value = True
REM Activate sheet for export - select "DataSheet"
   thisComponent.getCurrentController().setActiveSheet(thisComponent.getSheets().getByName(sSheetName))
REM storeToURL can raises com.sun.star.io.IOException! Only now:
On Error GoTo Errorhandle
REM Export
   thisComponent.storeToURL(FileN,storeParms())
   MsgBox ("No Error Found,Upload file is saved : """ + ConvertFromUrl(FileN) + """.")
Exit Sub

Errorhandle:
    MsgBox ("Modifications Are Not Saved,Upload File Not Generated" & chr(13) _
    & "May be table " & ConvertFromUrl(FileN) & " is open in another window?")
    Exit Sub
    Resume
End Sub

(It was posted on 18 Nov 2011 there)

Upvotes: 3

Related Questions