Batch converting .ods to .xls

Hello UBL TC,

I've just spent the afternoon recreating a functionality I was
using for UBL 2.0 to batch convert spreadsheets from .ods to .xls
(actually it was .xls to .ods the first time, but the basic idea
is the same), and I'm so annoyed with having to do this over again
that I'm posting the method to the list so that I'll always know
where to find it.  Most of you should probably ignore this, though
if anyone knows a better way to run the batch files, I'd be
interested to know what it is (and no, I don't think that running
another script to generate the commands is the way to do it).

Jon

##################################################################

HOW TO BATCH CONVERT .ODS FILES TO .XLS FILES USING OPENOFFICE

(Borrowing liberally from Bob DuCharme's article)

1. Make OO macro

Pick Macros from the OpenOffice Tools menu and then pick

Organize Macros, OpenOffice.org Basic, Organizer, and New to

create a new module. Name your module MyConversions and click

the Close button.

Select your MyConversions module and then click the Edit

button. In the script that comes up, you'll find the lines Sub

Main and End Main to get you started. Replace them with the

code shown here and then save your work:

' Save document as a Microsoft Excel file

Sub SaveAsExcel( cFile )

cURL = ConvertToURL( cFile )

oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, (_

Array(MakePropertyValue( "Hidden", True ),))

cFile = Left( cFile, Len( cFile ) - 4 ) + ".xls"

cURL = ConvertToURL( cFile )

oDoc.storeToURL( cURL, Array(_

MakePropertyValue( "FilterName", "MS Excel 97" ),)

oDoc.close( True )

End Sub

Function MakePropertyValue( Optional cName As String, Optional uValue ) _

As com.sun.star.beans.PropertyValue

Dim oPropertyValue As New com.sun.star.beans.PropertyValue

If Not IsMissing( cName ) Then
	 oPropertyValue.Name = cName

EndIf

If Not IsMissing( uValue ) Then
	 oPropertyValue.Value = uValue

EndIf

MakePropertyValue() = oPropertyValue

End Function

2. Running the macro

The problem here is that OO makes a URL out of the input file

name, so batching conversions is an enormous pain in the butt.

Here's what works on my system to convert UBL .ods files living

in the common and maindoc directories.  Note that all the file

names have to be absolute (gag, barf).

Batch file #1 (call it ods2xls-common.bat):

@echo off

"C:/Program Files/OpenOffice.org 2.3/program/soffice" -invisible macro:///Standard.MyConversions.SaveAsExcel(d:/ubl/updatepkg/image/mod/common/%1)

From within common, run like this:

for i in *; do echo $i; ../../../_tools/ods2xls-common.bat $i; done

Batch file #2 (call it ods2xls-maindoc.bat):

@echo off

"C:/Program Files/OpenOffice.org 2.3/program/soffice" -invisible macro:///Standard.MyConversions.SaveAsExcel(d:/ubl/updatepkg/image/mod/maindoc/%1)

From within maindoc, run like this:

for i in *; do echo $i; ../../../_tools/ods2xls-maindoc.bat $i; done