-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.
Automate save multiple Calc tabs
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Automate save multiple Calc tabs
openoffice 4.1.2 windows 10
Re: automate save multiple Calc tabs
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)?
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)?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: automate save multiple Calc tabs
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.
I generate a file per tab for importing into different applications.
openoffice 4.1.2 windows 10
Re: automate save multiple Calc tabs
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Automate save multiple Calc tabs
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:
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).
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
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Automate save multiple Calc tabs
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.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München