Macro to save all sheets as csv from source in a folder

Creating a macro - Writing a Script - Using the API

Macro to save all sheets as csv from source in a folder

Postby cartoonjazz » Thu Aug 22, 2019 8:59 pm

Hi, I'm new to macros and have rudimentary understanding of programming does anyone have a suggestion re the following requirements

-I have a workbook with 9 sheets where the data in each sheet is generated using formulas from "sheet 2"
-in some cases I have to use multiple source sheet,"sheet 2", data since calc crashes when I exceed 2000 lines.

I would like to put all 5 source sheets in a folder and with a macro:
-have calc read the data off source sheet 1 and write it into the workbook's sheet 2 and save all sheets as csv tab delimited files where the name on each file corresponds to the sheet name
- then clear the contents of sheet 2 and wash rinse repeat for all files in the source folder naming the save files the sheet name with an incremented number i.e. 2nd source file all files have the number 2 at the end of the file name etc.

thank you for your help
Last edited by cartoonjazz on Fri Aug 23, 2019 5:23 pm, edited 2 times in total.
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm

Re: macro to save all sheets as csv from source in a folder

Postby Zizi64 » Thu Aug 22, 2019 9:41 pm

Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8358
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro to save all sheets as csv from source in a folder

Postby cartoonjazz » Thu Aug 22, 2019 10:08 pm

Thank you, I saw that topic. It covers how to save all the sheets but not how to, through the macro, read all the source files from a folder and write into sheet 2 and then do the saving incrementally.
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 42
Joined: Sun Sep 04, 2016 9:40 pm

Re: macro to save all sheets as csv from source in a folder

Postby UnklDonald418 » Sat Aug 31, 2019 6:09 pm

Whenever I see a situation like yours that requires complex Calc macros I wonder it a database would be a better tool than a spreadsheet. Databases are much more adept at handling large data sets.
It is common on this forum to find someone attempting to use Calc to do things it was never designed to do, probably because spreadsheets are fairly intuitive and most users are somewhat familiar with them.
However, without a better understanding of what you are actually trying to accomplish I must assume that a spreadsheet is an acceptable tool.

The macro you describe would be an ambitious project even for someone with programming experience and a good knowledge of the OO/LO API. I suspect even then, there would be a significant amount of debugging required.

If you are planning on doing macro programming in OO/LO I would recommend learning to use the MRI object inspection tool extension
[Tutorial] Introduction into object inspection with MRI
https://github.com/hanya/MRI/wiki

That said, here is something that might get you started, based what I found on page 66 of the "StarOffice Basic Programmer's Guide"
https://docs.oracle.com/cd/E19064-01/so7/817-1826/817-1826.pdf
along with what I found in chapter 12.4 of "OpenOffice.org Macros Explained" by Andrew Pitonyak
http://www.pitonyak.org/oo.php
While you are there, also download what he refers to as "English Macro Document" because it has a number of useful examples

The following macro will open all specified documents found in a specified directory.
Code: Select all   Expand viewCollapse view
Sub LoadDocsFrom(SourceDir as String, FileExt as String)

Dim oDoc as Object
Dim sURL As String
Dim sFileName As String 'Last name returned from DIR
Dim noArgs()

'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
'      Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'oMRI = CreateUnoService( "mytools.Mri" )

 
sFileName = Dir(SourceDir & "*." & FileExt, 0)

While (sFileName <> "") 'While something returned
   sURL =  ConvertToURL(SourceDir & sFileName)
   oDoc = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, noArgs())
REM any document processing would probably be done here.
oMRI.inspect oDoc
REM you may want to close the document before loading the next.
'    oDoc.close(False)
   sFileName = Dir() 'Get the next name
Wend

End Sub

Then in some other Sub
Code: Select all   Expand viewCollapse view
LoadDocsFrom("C:\Documents\SSdatafiles\", "ods")

would open all the .ods documents in the specified folder.

I also wonder if it might be possible to use links to the external sheets rather than loading all the documents and copying the sheets. You can find maro code for updating spreadsheet links at
viewtopic.php?f=20&t=6004#p28037
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1225
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests