Automate save multiple Calc tabs

[cartoonjazz]

-I have created a multi tab calc file that generates custom calculations per tab.
-I paste a source file into tab "source" then I have to save each tab seperately i.e. tab "a" should be saved as "a.csv" tab seperated value using western (latin encoding), tab b, c ,d,,,the same,etc.
-I have to do this regularly and the process of manually pasting and saving is a chore that takes time since each tab has many calculations.

Is there a coded macro functionality in openoffice calc to automate this process for me? If you have a suggestion re what coding I could use it would be much appreciated.

[RusselB]

I strongly doubt that this functionality, as you describe it, has already been pre-coded.
You could, of course, make your own macro, possibly using the macro recorder, though in my experience the macro recorder is only good for limited applications.
What you're wanting might be beyond the capabilities of the macro recorder.
Might I ask why you need to save each tab as a .csv file, when all of the tabs would be saved using the default .ods file format by using Ctrl+S (or File -> Save)?

[cartoonjazz]

I'm willing to code it myself. Does the macro recorder give you the option of coding the macro or does it just record what you do?
I generate a file per tab for importing into different applications.

[RusselB]

The macro recorder just records what you do from the time you start the recorder until you stop it.
Some people, especially beginners, find it easier to start with a code that has been recorded, then, if necessary, edited to accommodate commands that the recorder doesn't handle well.
If you don't have any previous experience coding for OpenOffice, then you are (likely) in for a lot of learning.

[Lupp]

The recorder hasn't a slot working like the document method StoreToUrl. StroreAsUrl isn't appropriate for the task. Anyway a well designed "macro" is better here.
Roughly doing what the OQ wants:

Code: Select all

Sub saveSheetsExceptToCsv(Optional pBaseUrl As String, Optional pExcept As String)
If IsMissing(pExcept) Then pExcept = "source"
doc0 = ThisComponent
If IsMissing(pBaseUrl) Then pBaseUrl = doc0.Url
oldSel = doc0.CurrentSelection
doc0.Store()
theCC = doc0.CurrentController
theSheets = doc0.Sheets
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,ANSI,1,,0,false,true,true"
For Each oneSheet In theSheets
  If NOT (Instr(pExcept, oneSheet.Name)=1) Then
    theCC.Select(oneSheet)
    oneSheetNumber = oneSheet.RangeAddress.Sheet + 1
    sheetUrl = pBaseUrl & "." & oneSheetNumber & ".csv"
    doc0.StoreToUrl(sheetUrl, Args)
  End If
Next oneSheet
theCC.Select(oldSel)
End Sub

I wouln't use SheetNames for the files to create because I've seen so many absurd SheetNames not suitable for the purpose. I use instead the sheet numbers (Calc like: starting with 1).

[Lupp]

Sorry. Concerning the 9 parameters you have to pass to the csv export filter as FilterOptions I missed to point you to
https://wiki.openoffice.org/wiki/Docume ... CSV_Filter .

Concerning the field separator I would suggest to use the semicolon (ASCII 59) instead of the comma (ASCII 44) to avoid conflicts with the decimal separaor which may be a comma in some of the exported sheets depending on the local preferences of people having to work with the created files.