Open Office Automation from VFP 1

[Kurjo]

I am using Microsoft Excel for all our reporting needs which is run from Visual Foxpro application - using automation. This is how I call the excel and run a macro to format or to do any other changes in the report.

ocMacfile = " macro file name e.g c:\macros\testreport.xls "
ocMacsub = "sub routine name in the macro e.g testreport "
oExcel = CREATEOBJECT("Excel.Application")
oExcel.Workbooks.OPEN(ocMacfile)
oExcel.APPLICATION.RUN("&ocMacsub", lcInputFile, ocOutfile,
ocHd00,ocHd0,ocHd1,ocHd2,ocHd3,ocHd4)

I pass various parameters to the macro to format the report.

Now our company is planning to move to Open Office and I would like to know any one have done this type of automation from Visual FoxPro using Open office.
Any help or suggetion greatly appreciated.

Kurjo

 

[DSummZZZ]

The first step is too see if Open Office is an automation server.
I'm not sure that it is, since it's not a Microsoft product.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[bborissov]

Yes, it is, but believe me, the COM architecture is a nightmare

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Kurjo]

I was going through openoffice.org website. There lot of articles and samples to automate OO, but a very few related to Visual Foxpro.

I can create new OO documents or open existing OO documents from VFP, but I did not find a way to run a Macro by passing parameters.

Borislav Borissov have you done anything to automate open office. If so, can you post it here, may be I can figure out something out of it.

 

[bborissov]

Check this:

http://www.tedroche.com/Present/VFPOOoAutomation.htm

I tried to build a MailMerge which is relatively easy with MS Office, but I can't manage it in OO. I can create main document, can create Database BUT I can't relate both to new document.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

BTW Google: "OpenOffice VFP" and you will see how many results.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ronald777]

an example in OO, what works for me

***	Here is a very similar Visual FoxPro - Open Office example that..... 
***	1. Creates an OOo Calc spreadsheet 
***	2. Puts some strings into it. 
***	3. Puts some numbers into it. 
***	4. Puts some dates into it (as formulas) 
***	5. Saves it in Calc format (as C:\Example.sxw) 
***	6. Saves it in Excel format (as C:\Example.xls) 
***	7. Closes the document -- but this line is commented to leave the spreadsheet open in OOo 

***	Code: 
VfpOOoCalcExample() 

PROCEDURE VfpOOoCalcExample() 
   * Create a spreadsheet. 
   LOCAL oDoc 
   
   oDoc = OOoOpenURL( "private:factory/scalc" ) 
    
   * Get first sheet 
   LOCAL oSheet 
   oSheet = oDoc.getSheets().getByIndex( 0 ) 
    
   *----- 
   * Put some sales figures onto the sheet. 
   oSheet.getCellByPosition( 0, 0 ).setString( "Month" ) 
   oSheet.getCellByPosition( 1, 0 ).setString( "Sales" ) 
   oSheet.getCellByPosition( 2, 0 ).setString( "End Date" ) 
    
   oSheet.getCellByPosition( 0, 1 ).setString( "Jan" ) 
   oSheet.getCellByPosition( 0, 2 ).setString( "Feb" ) 
   oSheet.getCellByPosition( 0, 3 ).setString( "Mar" ) 
   oSheet.getCellByPosition( 0, 4 ).setString( "Apr" ) 
   oSheet.getCellByPosition( 0, 5 ).setString( "May" ) 
   oSheet.getCellByPosition( 0, 6 ).setString( "Jun" ) 
   oSheet.getCellByPosition( 0, 7 ).setString( "Jul" ) 
   oSheet.getCellByPosition( 0, 8 ).setString( "Aug" ) 
   oSheet.getCellByPosition( 0, 9 ).setString( "Sep" ) 
   oSheet.getCellByPosition( 0, 10 ).setString( "Oct" ) 
   oSheet.getCellByPosition( 0, 11 ).setString( "Nov" ) 
   oSheet.getCellByPosition( 0, 12 ).setString( "Dec" ) 
    
   oSheet.getCellByPosition( 1, 1 ).setValue( 3826.37 ) 
   oSheet.getCellByPosition( 1, 2 ).setValue( 3504.21 ) 
   oSheet.getCellByPosition( 1, 3 ).setValue( 2961.45 ) 
   oSheet.getCellByPosition( 1, 4 ).setValue( 2504.12 ) 
   oSheet.getCellByPosition( 1, 5 ).setValue( 2713.98 ) 
   oSheet.getCellByPosition( 1, 6 ).setValue( 2448.17 ) 
   oSheet.getCellByPosition( 1, 7 ).setValue( 1802.13 ) 
   oSheet.getCellByPosition( 1, 8 ).setValue( 2203.22 ) 
   oSheet.getCellByPosition( 1, 9 ).setValue( 1502.54 ) 
   oSheet.getCellByPosition( 1, 10 ).setValue( 1207.68 ) 
   oSheet.getCellByPosition( 1, 11 ).setValue( 1819.71 ) 
   oSheet.getCellByPosition( 1, 12 ).setValue( 986.03 ) 
    
   oSheet.getCellByPosition( 2, 1 ).setFormula( "=DATE(2004;01;31)" ) 
   oSheet.getCellByPosition( 2, 2 ).setFormula( "=DATE(2004;02;29)" ) 
   oSheet.getCellByPosition( 2, 3 ).setFormula( "=DATE(2004;03;31)" ) 
   oSheet.getCellByPosition( 2, 4 ).setFormula( "=DATE(2004;04;30)" ) 
   oSheet.getCellByPosition( 2, 5 ).setFormula( "=DATE(2004;05;31)" ) 
   oSheet.getCellByPosition( 2, 6 ).setFormula( "=DATE(2004;06;30)" ) 
   oSheet.getCellByPosition( 2, 7 ).setFormula( "=DATE(2004;07;31)" ) 
   oSheet.getCellByPosition( 2, 8 ).setFormula( "=DATE(2004;08;31)" ) 
   oSheet.getCellByPosition( 2, 9 ).setFormula( "=DATE(2004;09;30)" ) 
   * Note that these last three dates are not set as DATE() function calls. 
   oSheet.getCellByPosition( 2, 10 ).setFormula( "10/31/2004" ) 
   oSheet.getCellByPosition( 2, 11 ).setFormula( "11/30/2004" ) 
   oSheet.getCellRangeByName( "C13" ).setFormula( "12/31/2004" ) 
   *----- 
    
   * Format the date cells as dates.    
   oFormats = oDoc.getNumberFormats() 
   oLocale = OOoCreateStruct( "com.sun.star.lang.Locale" ) 
   * com.sun.star.util.NumberFormat.DATE = 2 
   nDateKey = oFormats.getStandardFormat( 2, oLocale ) 
   oCell = oSheet.getCellRangeByName( "C2:C13" ) 
   oCell.NumberFormat = nDateKey 
    
   LOCAL ARRAY aOneArg[1] 
   LOCAL cFile, cURL 
    
*   cFile = GetDesktopFolderPathname()+"example" 
   cFile = "c:\example" 

   * Now save the spreadsheet. 
   cURL = OOoConvertToURL( cFile + ".sxw" ) 
   aOneArg[1] = OOoMakePropertyValue( "Overwrite", .T. ) 
   oDoc.storeToUrl( cURL, @ aOneArg ) 
    
   * Now save it as Excel 
   cURL = OOoConvertToURL( cFile + ".xls" ) 
   aOneArg[1] = OOoMakePropertyValue( "FilterName", "MS Excel 97" ) 
   oDoc.storeToUrl( cURL, @ aOneArg ) 
    
   * Close the document. 
*   oDoc.close( 1 ) && TRUE 
ENDPROC 





* Open or Create a document from it's URL. 
* New documents are created by URL's such as: 
*   private:factory/sdraw 
*   private:factory/swriter 
*   private:factory/scalc 
*   private:factory/simpress 
FUNCTION OOoOpenURL( cURL ) 
*   LOCAL oPropertyValue 
*   oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" ) 

*   LOCAL ARRAY aNoArgs[1] 
*   aNoArgs[1] = oPropertyValue 
*   aNoArgs[1].Name = "ReadOnly" 
*   aNoArgs[1].Value = .F. 

   * These two lines replace the alternate version above, 
   *  which are left commented for the insight they provide. 
   LOCAL ARRAY aNoArgs[1] 
   aNoArgs[1] = OOoMakePropertyValue( "Hidden", .F. ) 
    
   LOCAL oDesktop 
   oDesktop = OOoGetDesktop() 

   LOCAL oDoc 
   oDoc = oDesktop.LoadComponentFromUrl( cURL, "_blank", 0, @ aNoargs ) 
    
   * Make sure that arrays passed to this document are passed zero based. 
   COMARRAY( oDoc, 10 ) 
    
   RETURN oDoc 
ENDFUNC 

* Create a com.sun.star.beans.PropertyValue struct and return it. 
FUNCTION OOoMakePropertyValue( cName, uValue, nHandle, nState ) 
   LOCAL oPropertyValue 
   oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" ) 
    
   oPropertyValue.Name = cName 
   oPropertyValue.Value = uValue 
    
   IF TYPE([nHandle])="N" 
      oPropertyValue.Handle = nHandle 
   ENDIF 
   IF TYPE([nState])="N" 
      oPropertyValue.State = nState 
   ENDIF 
    
   RETURN oPropertyValue 
ENDFUNC 



* Sugar coated routine to create any UNO struct. 
* Use the Bridge_GetStruct() feature of the OLE-UNO bridge. 
FUNCTION OOoCreateStruct( cTypeName ) 
   LOCAL oServiceManager 
   oServiceManager = OOoGetServiceManager() 
    
   LOCAL oStruct 
   oStruct = .NULL. 

   LOCAL cOldErrHandler 
   cOldErrHandler = ON( "ERROR" ) 
   ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) ) 
      oStruct = oServiceManager.Bridge_GetStruct( cTypeName ) 
   ON ERROR &cOldErrHandler 
    
   IF ISNULL( oStruct ) 
      =__OOoReleaseCachedVars() 
      oServiceManager = OOoGetServiceManager() 
      oStruct = oServiceManager.Bridge_GetStruct( cTypeName ) 
   ENDIF 

   RETURN oStruct 
ENDFUNC 


* Return the OpenOffice.org desktop object. 
* Cache it in a global variable. 
* Create it if not already cached. 
FUNCTION OOoGetDesktop() 
   IF (TYPE([goOOoDesktop])!="O")  OR  ISNULL( goOOoDesktop ) 
      PUBLIC goOOoDesktop 
      goOOoDesktop = OOoServiceManager_CreateInstance( "com.sun.star.frame.Desktop" ) 
      COMARRAY( goOOoDesktop, 10 ) 
   ENDIF 
   RETURN goOOoDesktop 
ENDFUNC 



* Return the OpenOffice.org service manager object. 
* Cache it in a global variable. 
* Create it if not already cached. 
FUNCTION OOoGetServiceManager() 
   IF (TYPE([goOOoServiceManager])!="O")  OR  ISNULL( goOOoServiceManager ) 
      PUBLIC goOOoServiceManager 
      goOOoServiceManager = CREATEOBJECT( "com.sun.star.ServiceManager" ) 
   ENDIF 
   RETURN goOOoServiceManager 
ENDFUNC 


* Sugar coated routine to ask the service manager to 
*  create you an instance of some other OpenOffice.org UNO object. 
FUNCTION OOoServiceManager_CreateInstance( cServiceName ) 
   LOCAL oServiceManager 
   oServiceManager = OOoGetServiceManager() 
    
   LOCAL oInstance 
   oInstance = .NULL. 

   LOCAL cOldErrHandler 
   cOldErrHandler = ON( "ERROR" ) 
   ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) ) 
      oInstance = oServiceManager.createInstance( cServiceName ) 
   ON ERROR &cOldErrHandler 
    
   IF ISNULL( oInstance ) 
      =__OOoReleaseCachedVars() 
      oServiceManager = OOoGetServiceManager() 
      oInstance = oServiceManager.createInstance( cServiceName ) 
   ENDIF 

   RETURN oInstance 
ENDFUNC 


PROCEDURE DoNothing__ErrorHandler( pnError, pcErrMessage, pnLineNo, pcProgramFileSys16, pcProgram, pcErrorParamSys2018 ) 
ENDPROC 


PROCEDURE __OOoReleaseCachedVars() 
   RELEASE goOOoServiceManager, goOOoDesktop, goOOoCoreReflection 
ENDPROC 


* Convert a local filename to an OOo URL. 
FUNCTION OOoConvertToURL( cFilename ) 
   * Ensure leading slash. 
   IF LEFT( cFilename, 1 ) != "/" 
      cFileName = "/" + cFileName 
   ENDIF 
    
   LOCAL cURL 
   cURL = CHRTRAN( cFilename, "\", "/" )   && change backslashes to forward slashes. 
   cURL = "file://" + cURL 
   RETURN cURL 
ENDFUNC

 

[stirfry]

Thanks ronald777. star for you.

Thats the best conversion I have ever seen!

You should write it up as a FAQ

Andrew

 

[Nifrabar]

I once found this code somewhere on the net.
Maybe this helps you again a step further.
Calc is included as well.
-Bart

*
*	Library of routines to manipulate OpenOffice.org.
*
*
* [URL unfurl="true"]http://kosh.datateamsys.com/~danny/OOo/VisualFoxPro-OOo/LibOOo-2003-11-17-01.prg[/URL]

*############################################################
*	Public API
*	High level routines.
*############################################################


* Return .T. if OpenOffice.org is installed.
* Return .F. if not installed.
FUNCTION OOoIsInstalled()
	LOCAL oServiceManager
	oServiceManager = .NULL.

	LOCAL cOldErrHandler
	cOldErrHandler = ON( "ERROR" )
	ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
		oServiceManager = OOoGetServiceManager()
	ON ERROR &cOldErrHandler
	
	* If we could create a Service Manager,
	*  then OpenOffice.org must be installed.
	RETURN NOT ISNULL( oServiceManager )
ENDFUNC




* Easy way to create a new Draw document.
FUNCTION OOoCreateNewDrawDocument()
	LOCAL oDrawDoc
	oDrawDoc = OOoOpenURL( "private:factory/sdraw" )
	RETURN oDrawDoc
ENDFUNC


* Easy way to create a new Writer document.
FUNCTION OOoCreateNewWriterDocument()
	LOCAL oWriterDoc
	oWriterDoc = OOoOpenURL( "private:factory/swriter" )
	RETURN oWriterDoc
ENDFUNC


* Easy way to create a new Calc document.
FUNCTION OOoCreateNewCalcDocument()
	LOCAL oCalcDoc
	oCalcDoc = OOoOpenURL( "private:factory/scalc" )
	RETURN oCalcDoc
ENDFUNC


* Easy way to create a new Impress document.
FUNCTION OOoCreateNewImpressDocument()
	LOCAL oImpressDoc
	oImpressDoc = OOoOpenURL( "private:factory/simpress" )
	RETURN oImpressDoc
ENDFUNC



FUNCTION OOoOpenFile( cFilename )
	LOCAL cURL
	cURL = OOoConvertToURL( cFilename )
	
	LOCAL oDoc
	oDoc = OOoOpenURL( cURL )
	RETURN oDoc
ENDFUNC


PROCEDURE OOoTerminateProgram()
	LOCAL oDesktop
	oDesktop = OOoGetDesktop()
	oDesktop.Terminate()
	
	=__OOoReleaseCachedVars()
ENDPROC


* Convert a local filename to an OOo URL.
FUNCTION OOoConvertToURL( cFilename )
	* Ensure leading slash.
	IF LEFT( cFilename, 1 ) != "/"
		cFileName = "/" + cFileName
	ENDIF
	
	LOCAL cURL
	cURL = CHRTRAN( cFilename, "\", "/" )	&& change backslashes to forward slashes.
	cURL = "file://" + cURL
	RETURN cURL
ENDFUNC




*############################################################
*	Draw specific tools
*############################################################


* Page zero is the first page.
* Once you have a drawing document, obtain one of its pages.
FUNCTION OOoGetDrawPage( oDrawDoc, nPageNum )
	LOCAL oPages
	oPages = oDrawDoc.GetDrawPages()
	LOCAL oPage
	oPage = oPages.GetByIndex( nPageNum )
	RETURN oPage
ENDFUNC


* Given a draw document, create a shape.
* The shape must be added to the draw page, by calling
*  the draw page's Add() method.
* The oPosition and oSize arguments are optional, but you may
*  pass a Point and Size struct for these arguments.

FUNCTION OOoMakeRectangleShape( oDrawDoc, oPosition, oSize )
	LOCAL oShape
	oShape = OOoMakeShape( oDrawDoc, "com.sun.star.drawing.RectangleShape", oPosition, oSize )
	RETURN oShape
ENDFUNC

FUNCTION OOoMakeEllipseShape( oDrawDoc, oPosition, oSize )
	LOCAL oShape
	oShape = OOoMakeShape( oDrawDoc, "com.sun.star.drawing.EllipseShape", oPosition, oSize )
	RETURN oShape
ENDFUNC

FUNCTION OOoMakeLineShape( oDrawDoc, oPosition, oSize )
	LOCAL oShape
	oShape = OOoMakeShape( oDrawDoc, "com.sun.star.drawing.LineShape", oPosition, oSize )
	RETURN oShape
ENDFUNC

FUNCTION OOoMakeTextShape( oDrawDoc, oPosition, oSize )
	LOCAL oShape
	oShape = OOoMakeShape( oDrawDoc, "com.sun.star.drawing.TextShape", oPosition, oSize )
	RETURN oShape
ENDFUNC


* Create and return a Point struct.
FUNCTION OOoPosition( nX, nY )
	LOCAL oPoint
	oPoint = OOoCreateStruct( "com.sun.star.awt.Point" )
	oPoint.X = nX
	oPoint.Y = nY
	RETURN oPoint
ENDFUNC

* Create and return a Size struct.
FUNCTION OOoSize( nWidth, nHeight )
	LOCAL oSize
	oSize = OOoCreateStruct( "com.sun.star.awt.Size" )
	oSize.Width = nWidth
	oSize.Height = nHeight
	RETURN oSize
ENDFUNC


* Given a shape, alter its position.
PROCEDURE OOoSetPosition( oShape, nX, nY )
	LOCAL oPosition
	oPosition = oShape.Position
	oPosition.X = nX
	oPosition.Y = nY
	oShape.Position = oPosition
ENDPROC

* Given a shape, alter its size.
PROCEDURE OOoSetSize( oShape, nWidth, nHeight )
	LOCAL oSize
	oSize = oShape.Size
	oSize.Width = nWidth
	oSize.Height = nHeight
	oShape.Size = oSize
ENDPROC


* Given a draw document, create a shape.
* Optional:  oPosition, oSize can receive a Point or Size struct.
FUNCTION OOoMakeShape( oDrawDoc, cShapeClassName, oPosition, oSize )
	LOCAL oShape
	oShape = oDrawDoc.CreateInstance( cShapeClassName )
	
	IF (TYPE([oPosition])="O")  AND  (NOT ISNULL( oPosition ))
		oShape.Position = oPosition
	ENDIF
	IF (TYPE([oSize])="O")  AND  (NOT ISNULL( oSize ))
		oShape.Size = oSize
	ENDIF
	
	RETURN oShape
ENDFUNC



*############################################################
*	Color conversion utilities
*	--------------------------
*	Visual FoxPro and OpenOffice.org do not represent
*	 color values the same way.
*	Conversion routines in both directions are provided
*	 to keep things easy.
*	In addition, extremely convenient HSV routines are provided.
*	See LibGraphics for discussion of HSV color model.
*############################################################


* Similar to VFP's built in RGB() function.
* Pass in R,G,B values and out comes an OpenOffice.org color value.
* Note that this is DIFFERENT from how VFP constructs color values.
FUNCTION OOoRGB( nRed, nGreen, nBlue )
	RETURN BITOR( BITOR( BITLSHIFT( nRed, 16 ), BITLSHIFT( nGreen, 8 ) ), nBlue )
ENDFUNC

* Translate between a Visual FoxPro color and an OpenOffice.org color
*  by using a simple formula.
* Pass in a VFP color, out comes an OOo color.  And vice versa.
FUNCTION OOoColor( nColor )
	LOCAL nTranslatedColor
	nTranslatedColor = (INT( nColor / 65536 )) + (INT( INT( nColor / 256 ) % 256 ) * 256) + (INT( nColor % 256 ) * 65536)
	RETURN nTranslatedColor
ENDFUNC

* Extract the Red component from an OpenOffice.org color.
* SImilar to the RGBRed() function in LibGraphics.
FUNCTION OOoRed( nOOoColor )
*	RETURN INT( nOOoColor / 65536 )
	RETURN BITRSHIFT( BITAND( nOOoColor, 0x00FF0000 ), 16 )
ENDFUNC

* Extract the Green component from an OpenOffice.org color.
* SImilar to the RGBGreen() function in LibGraphics.
FUNCTION OOoGreen( nOOoColor )
*	RETURN INT( INT( nOOoColor / 256 ) % 256 )
	RETURN BITRSHIFT( BITAND( nOOoColor, 0x0000FF00 ), 8 )
ENDFUNC

* Extract the Blue component from an OpenOffice.org color.
* SImilar to the RGBBlue() function in LibGraphics.
FUNCTION OOoBlue( nOOoColor )
*	RETURN INT( nOOoColor % 256 )
	RETURN BITAND( nOOoColor, 0x000000FF )
ENDFUNC

* Convenient way to construct an OOo color from HSV components.
* See LibGraphics for information about HSV.
* Note nHue is a number from 0.0 to 1.0.
FUNCTION OOoHSV( nHue, nSaturation, nBrightness )
	LOCAL nColor
	nColor = MakeHSVColor( nHue * 6.0, nSaturation, nBrightness )
	nColor = OOoColor( nColor )
	RETURN nColor
ENDFUNC

* Convenient way to extract the Hue component from an OOo color.
* See LibGraphics for information about HSV.
* Note nHue is a number from 0.0 to 1.0.
FUNCTION OOoHue( nOOoColor )
	RETURN HSVHue( OOoColor( nOOoColor ) ) / 6.0
ENDFUNC

* Convenient way to extract the Saturation component from an OOo color.
* See LibGraphics for information about HSV.
FUNCTION OOoSaturation( nOOoColor )
	RETURN HSVSaturation( OOoColor( nOOoColor ) )
ENDFUNC

* Convenient way to extract the Brightness component from an OOo color.
* See LibGraphics for information about HSV.
FUNCTION OOoBrightness( nOOoColor )
	RETURN HSVValue( OOoColor( nOOoColor ) )
ENDFUNC



*############################################################



PROCEDURE DoNothing__ErrorHandler( pnError, pcErrMessage, pnLineNo, pcProgramFileSys16, pcProgram, pcErrorParamSys2018 )
ENDPROC


* Return the OpenOffice.org service manager object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetServiceManager()
	IF (TYPE([goOOoServiceManager])!="O")  OR  ISNULL( goOOoServiceManager )
		PUBLIC goOOoServiceManager
		goOOoServiceManager = CREATEOBJECT( "com.sun.star.ServiceManager" )
	ENDIF
	RETURN goOOoServiceManager
ENDFUNC


* Sugar coated routine to ask the service manager to
*  create you an instance of some other OpenOffice.org UNO object.
FUNCTION OOoServiceManager_CreateInstance( cServiceName )
	LOCAL oServiceManager
	oServiceManager = OOoGetServiceManager()
	
	LOCAL oInstance
	oInstance = .NULL.

	LOCAL cOldErrHandler
	cOldErrHandler = ON( "ERROR" )
	ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
		oInstance = oServiceManager.createInstance( cServiceName )
	ON ERROR &cOldErrHandler
	
	IF ISNULL( oInstance )
		=__OOoReleaseCachedVars()
		oServiceManager = OOoGetServiceManager()
		oInstance = oServiceManager.createInstance( cServiceName )
	ENDIF

	RETURN oInstance
ENDFUNC


* Sugar coated routine to create any UNO struct.
* Use the Bridge_GetStruct() feature of the OLE-UNO bridge.
FUNCTION OOoCreateStruct( cTypeName )
	LOCAL oServiceManager
	oServiceManager = OOoGetServiceManager()
	
	LOCAL oStruct
	oStruct = .NULL.

	LOCAL cOldErrHandler
	cOldErrHandler = ON( "ERROR" )
	ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
		oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
	ON ERROR &cOldErrHandler
	
	IF ISNULL( oStruct )
		=__OOoReleaseCachedVars()
		oServiceManager = OOoGetServiceManager()
		oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
	ENDIF

	RETURN oStruct
ENDFUNC


* Return the OpenOffice.org desktop object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetDesktop()
	IF (TYPE([goOOoDesktop])!="O")  OR  ISNULL( goOOoDesktop )
		PUBLIC goOOoDesktop
		goOOoDesktop = OOoServiceManager_CreateInstance( "com.sun.star.frame.Desktop" )
		COMARRAY( goOOoDesktop, 10 )
	ENDIF
	RETURN goOOoDesktop
ENDFUNC


*!*	* Return an instance of com.sun.star.reflection.CoreReflection.
*!*	* Cache it in a global variable.
*!*	* Create it if not already cached.
*!*	FUNCTION OOoGetCoreReflection()
*!*		IF (TYPE([goOOoCoreReflection])!="O")  OR  ISNULL( goOOoCoreReflection )
*!*			PUBLIC goOOoCoreReflection
*!*			goOOoCoreReflection = OOoServiceManager_CreateInstance( "com.sun.star.reflection.CoreReflection" )
*!*			COMARRAY( goOOoCoreReflection, 10 )
*!*		ENDIF
*!*		RETURN goOOoCoreReflection
*!*	ENDFUNC


*!*	* Create a UNO struct object and return it.
*!*	* This routine is now obsolete.
*!*	* See a superior implementation of this routine elsewhere
*!*	*  which uses the Bridge_GetStruct() feature of the OLE-UNO bridge.
*!*	FUNCTION OOoCreateStruct( cTypeName )
*!*		* Ask service manager for a CoreReflection object.
*!*		LOCAL oCoreReflection
*!*		oCoreReflection = OOoGetCoreReflection()
*!*		
*!*		* Get the IDL Class for the type name.
*!*		LOCAL oXIdlClass
*!*		oXIdlClass = .NULL.

*!*		LOCAL cOldErrHandler
*!*		cOldErrHandler = ON( "ERROR" )
*!*		ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
*!*			oXIdlClass = oCoreReflection.forName( cTypeName )
*!*		ON ERROR &cOldErrHandler
*!*		
*!*		IF ISNULL( oXIdlClass )
*!*			=__OOoReleaseCachedVars()
*!*			oCoreReflection = OOoGetCoreReflection()
*!*			oXIdlClass = oCoreReflection.forName( cTypeName )
*!*		ENDIF
*!*		
*!*		* Create a variable to hold the created Struct.
*!*		* Assign it some initial value.
*!*		LOCAL oStruct
*!*		oStruct = CREATEOBJECT( "relation" ) && assign some kind of object initially

*!*		* Ask the class definition to create an instance.
*!*		oXIdlClass.CreateObject( @oStruct )
*!*		
*!*		RETURN oStruct
*!*	ENDFUNC


* Create a com.sun.star.beans.PropertyValue struct and return it.
FUNCTION OOoPropertyValue( cName, uValue, nHandle, nState )
	LOCAL oPropertyValue
	oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )
	
	oPropertyValue.Name = cName
	oPropertyValue.Value = uValue
	
	IF TYPE([nHandle])="N"
		oPropertyValue.Handle = nHandle
	ENDIF
	IF TYPE([nState])="N"
		oPropertyValue.State = nState
	ENDIF
	
	RETURN oPropertyValue
ENDFUNC



* Open or Create a document from it's URL.
* New documents are created by URL's such as:
*	private:factory/sdraw
*	private:factory/swriter
*	private:factory/scalc
*	private:factory/simpress
FUNCTION OOoOpenURL( cURL )
*	LOCAL oPropertyValue
*	oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )

*	LOCAL ARRAY aNoArgs[1]
*	aNoArgs[1] = oPropertyValue
*	aNoArgs[1].Name = "ReadOnly"
*	aNoArgs[1].Value = .F.

	* These two lines replace the alternate version above,
	*  which are left commented for the insight they provide.
	LOCAL ARRAY aNoArgs[1]
	aNoArgs[1] = OOoPropertyValue( "Hidden", .F. )
	
	LOCAL oDesktop
	oDesktop = OOoGetDesktop()

	LOCAL oDoc
	oDoc = oDesktop.LoadComponentFromUrl( cURL, "_blank", 0, @ aNoargs )
	
	* Make sure that arrays passed to this document are passed zero based.
	COMARRAY( oDoc, 10 )
	
	RETURN oDoc
ENDFUNC


PROCEDURE __OOoReleaseCachedVars()
	RELEASE goOOoServiceManager, goOOoDesktop, goOOoCoreReflection
ENDPROC



*############################################################


* Experimental stuff



*SET PROCEDURE TO LibOOo ADDITIVE
*oDoc = OOoOpenFile( GetDesktopFolderPathname() + "test.sxw" )


* This is an attempt to print a document.
PROCEDURE PrintExperiment( oDoc )
	LOCAL ARRAY aArgs[1]
	aArgs[1] = OOoPropertyValue( "CopyCount", 1, -1, 0 )
*	aArgs[1] = OOoPropertyValue( "Collate", .F. )
	
	oDoc.Print( @ aArgs )
ENDPROC


*############################################################


PROCEDURE TestOOoDraw()
	* Create a drawing.
	LOCAL oDoc
	oDoc = OOoCreateNewDrawDocument()
	
	* Get the first page of the drawing.
	LOCAL oPage
	oPage = OOoGetDrawPage( oDoc, 0 )
	
	LOCAL nHue, nSaturation, nBrightness
	LOCAL nRow, nCol
	LOCAL oShape

	* Now let's draw some rectangles.

	*-----
	*	Notes about HSV color notation.
	* Imagine hue is a "rainbow" of colors, from 0.0 to 1.0.
	* 0.0 is red, then you progress to orange, yellow, green, blue, purple, and
	*  then back to red again.  (Not like a real rainbow.)  1.0 is red also.
	* HSV colors are easy for HUMANS, while RGB colors are easy for COMPUTERS.
	* Quick, what is the RGB for Pink?  Brown?
	* In HSV, you would think Pink is a low-saturation red at full brightness,
	*  thus you could say H=0.0, S=0.5, B=1.0; and that's off the top of my head.
	* Brown is a low brightness Orange, so H=0.0888, S=1.0, B=0.6; right out of thin air.
	* Need a highly contrasting color?  Easy in HSV coordinates.
	* Just pick the opposite hue, maybe maximize saturation (opposite of other color),
	*  and maybe also a min or max brightness.  Can't easily come up with highly
	*  contrasting colors in RGB coordinates in RGB space.
	* Need to "soften" colors?  Take each HSV coordinate, and lower the saturation
	*  by 10 %.
	* Need to "darken" colors?  Lower the brightness of each pixel by 10 %.
	*-----
	
	* Let's vary the hue and saturation, while keeping the brightness constant.
	* But you could vary any two parameters, while keeping the third constant.
	
	nBrightness = 1.0 && full brightness
	FOR nRow = 0 TO 9
		FOR nCol = 0 TO 9
			* Create a shape
			oShape = OOoMakeRectangleShape( oDoc )
			* Calculate color
			nHue = nCol / 9 && hue varies *across* by column
			nSaturation = nRow / 9 && saturation varies *down* by row
			* set color of shape
			* oShape.FillColor = OOoRGB( 255, 255, 255 )
			oShape.FillColor = OOoHSV( nHue, nSaturation, nBrightness )
			* Position and size the shape
			OOoSetSize( oShape, 500, 500 ) && 1/2 cm by 1/2 cm
			OOoSetPosition( oShape, 1000 + nCol * 600, 1000 + nRow * 600 )
			* Add shape to page
			oPage.Add( oShape )
		ENDFOR
	ENDFOR


	* Let's vary the hue and saturation, while keeping the brightness constant.
	* This time, we'll use less-brightness
	* But you could vary any two parameters, while keeping the third constant.
	
	nBrightness = 0.7 && less brightness
	FOR nRow = 0 TO 9
		FOR nCol = 0 TO 9
			* Create a shape
			oShape = OOoMakeRectangleShape( oDoc )
			* Calculate color
			nHue = nCol / 9 && hue varies *across* by column
			nSaturation = nRow / 9 && saturation varies *down* by row
			* set color of shape
			oShape.FillColor = OOoHSV( nHue, nSaturation, nBrightness )
			* Position and size the shape
			OOoSetSize( oShape, 500, 500 ) && 1/2 cm by 1/2 cm
			OOoSetPosition( oShape, 9000 + nCol * 600, 1000 + nRow * 600 )
			* Add shape to page
			oPage.Add( oShape )
		ENDFOR
	ENDFOR


	* Let's vary the hue and brightness, while keeping the saturation constant.
	* But you could vary any two parameters, while keeping the third constant.
	
	nSaturation = 1.0 && full saturation
	FOR nRow = 0 TO 9
		FOR nCol = 0 TO 9
			* Create a shape
			oShape = OOoMakeRectangleShape( oDoc )
			* Calculate color
			nHue = nRow / 9 && hue varies *down* by row
			nBrightness = nCol / 9 && brightness varies *across* by column
			* set color of shape
			oShape.FillColor = OOoHSV( nHue, nSaturation, nBrightness )
			* Position and size the shape
			OOoSetSize( oShape, 500, 500 ) && 1/2 cm by 1/2 cm
			OOoSetPosition( oShape, 1000 + nCol * 600, 9000 + nRow * 600 )
			* Add shape to page
			oPage.Add( oShape )
		ENDFOR
	ENDFOR


	* Let's vary the saturation and brightness, while keeping the hue constant.
	* The hue will be yellow.
	* But you could vary any two parameters, while keeping the third constant.

	nHue = 0.1666 && yellow
	FOR nRow = 0 TO 9
		FOR nCol = 0 TO 9
			* Create a shape
			oShape = OOoMakeRectangleShape( oDoc )
			* Calculate color
			nSaturation = nRow / 9 && saturation varies *down* by row
			nBrightness = nCol / 9 && brightness varies *across* by column
			* set color of shape
			oShape.FillColor = OOoHSV( nHue, nSaturation, nBrightness )
			* Position and size the shape
			OOoSetSize( oShape, 500, 500 ) && 1/2 cm by 1/2 cm
			OOoSetPosition( oShape, 9000 + nCol * 600, 9000 + nRow * 600 )
			* Add shape to page
			oPage.Add( oShape )
		ENDFOR
	ENDFOR
	
	
	* Gee, you don't suppose someone out there could take the knowledge represented
	*  by this code and figure out how to make OpenOffice.org draw bar charts?
	
ENDPROC


PROCEDURE TestOOoCalc()
	* Create a spreadsheet.
	LOCAL oDoc
	oDoc = OOoCreateNewCalcDocument()
	
	* Get first sheet
	LOCAL oSheet
	oSheet = oDoc.getSheets().getByIndex( 0 )
	
	* Plug in some stuff...
	oSheet.getCellByPosition( 0, 0 ).setFormula( "Month" )
	oSheet.getCellByPosition( 1, 0 ).setFormula( "Sales" )
	oSheet.getCellByPosition( 0, 1 ).setFormula( "Jan" )
	oSheet.getCellByPosition( 0, 2 ).setFormula( "Feb" )
	oSheet.getCellByPosition( 0, 3 ).setFormula( "Mar" )
	oSheet.getCellByPosition( 0, 4 ).setFormula( "Apr" )
	oSheet.getCellByPosition( 0, 5 ).setFormula( "May" )
	oSheet.getCellByPosition( 0, 6 ).setFormula( "Jun" )
	oSheet.getCellByPosition( 1, 1 ).setValue( 3826 )
	oSheet.getCellByPosition( 1, 2 ).setValue( 3504 )
	oSheet.getCellByPosition( 1, 3 ).setValue( 2961 )
	oSheet.getCellByPosition( 1, 4 ).setValue( 2504 )
	oSheet.getCellByPosition( 1, 5 ).setValue( 2102 )
	oSheet.getCellByPosition( 1, 6 ).setValue( 1756 )
	
	LOCAL ARRAY aOneArg[1]
	LOCAL cFile, cURL
	
*	cFile = GetDesktopFolderPathname()+"example"
	cFile = "c:\example"

	* Now save the spreadsheet.
	cURL = OOoConvertToURL( cFile + ".sxw" )
	aOneArg[1] = OOoPropertyValue( "Overwrite", .T. )
	oDoc.storeToUrl( cURL, @ aOneArg )
	
	* Now save it as Excel
	cURL = OOoConvertToURL( cFile + ".xls" )
	aOneArg[1] = OOoPropertyValue( "FilterName", "MS Excel 97" )
	oDoc.storeToUrl( cURL, @ aOneArg )
ENDPROC




PROCEDURE CreateHueRainbow()
	* Create a drawing.
	LOCAL oDoc
	oDoc = OOoCreateNewDrawDocument()
	
	* Get the first page of the drawing.
	LOCAL oPage
	oPage = OOoGetDrawPage( oDoc, 0 )
	
	LOCAL nHue, nSaturation, nBrightness
	LOCAL nCol
	LOCAL oShape

	* Now let's draw some rectangles.

	* Let's vary the hue and saturation, while keeping the brightness constant.
	* But you could vary any two parameters, while keeping the third constant.
	
	nBrightness = 1.0 && full brightness
	nSaturation = 1.0 && full saturation
	FOR nCol = 0 TO 200
		* Create a shape
		oShape = OOoMakeRectangleShape( oDoc )
		* Calculate color
		nHue = nCol / 200 && hue varies *across* by column
		* set color of shape
		* oShape.FillColor = OOoRGB( 255, 255, 255 )
		oShape.FillColor = OOoHSV( nHue, nSaturation, nBrightness )
		* Position and size the shape
		OOoSetSize( oShape, 52, 2000 ) && 0.05 cm by 2 cm
		OOoSetPosition( oShape, 1000 + nCol * 50, 1000 )
		oShape.LineStyle = 0
		* Add shape to page
		oPage.Add( oShape )
	ENDFOR
	
	LOCAL cFile, cURL
*	cFile = GetDesktopFolderPathname()+"hue rainbow"
	cFile = "c:\hue rainbow"
	cURL = OOoConvertToURL( cFile + ".sxd" )
	LOCAL ARRAY aOneArg[1]
	aOneArg[1] = OOoPropertyValue( "Overwrite", .T. )
	oDoc.storeAsUrl( cURL, @ aOneArg )
*	oDoc.dispose()
ENDPROC

 

[Kurjo]

Thanks for the posing. I saw some of these codes in the oOO website. But none of these help my need. I need to execute a OO macro by passing some parameters. Since we can manipulate OO documents from VFP I have a confidence that we can do that just like MS Office. So I am still searching the clues.

Thanks you all.