Batch converting .ods to .xls

[jon.bosak@sun.com]

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