ubl — archive

[Jon Bosak]

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