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

[cartoonjazz]

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

[Zizi64]

viewtopic.php?f=45&t=19695

[cartoonjazz]

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.

[UnklDonald418]

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/so ... 7-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

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

 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