Apache OpenOffice (AOO) Bugzilla – Issue 40280
createReplaceDescriptor does not work with OLE Automation
Last modified: 2013-02-24 21:07:39 UTC
I want to make a VBA macro in MS Word that makes OOo make some changes in a Calc file with the search and replace function. This should be possible using Windows OLE Automation, but obviously it doesn't work. My code looks like this (in Words' VBA editor): Sub Example() Dim oServiceManager As Object Set oServiceManager = CreateObject("com.sun.star.ServiceManager") Dim oDesktop as Object Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop") 'Open a new document based on a template. Dim sTemplatePath As String Dim oDocument As Object Dim oSheet As Object oTemplatePath = "file:///C:/SomePath/Example.stc" Set oDocument = oDesktop.loadComponentFromURL(sTemplatePath, "_blank", 0, args) Set oSheet = oDocument.Sheets(0) If oSheet.supportsService("com.sun.star.util.createReplaceDescriptor") Then MsgBox "The service is supported." 'No msgbox is shown. End If 'Modify document content Dim oReplace As Object Set oReplace = oSheet.createReplaceDescriptor 'Fails! Set oReplace.SearchString = "Old text" Set oReplace.ReplaceString = "New text" oSheet.ReplaceAll (oReplace) End Sub When I make the coresponding macro in OOo itself, it works fine.
sw->jl: as far as I know the OLE-automation is your area. I could reproduce the behaviour with src680_m68
The corresponding OOo Basic macro, which works without Error message would be Dim sTemplatePath As String Dim oDocument As Object Dim oSheet As Object sTemplatePath = "file:///C:/SomePath/Example.stc" oDocument = StarDesktop.loadComponentFromURL(sTemplatePath, "_blank", 0, dimArray()) oSheet = oDocument.Sheets(0) If oSheet.supportsService("com.sun.star.util.createReplaceDescriptor") Then MsgBox "The service is supported." 'No msgbox is shown. End If 'Modify document content Dim oReplace As Object oReplace = oSheet.createReplaceDescriptor oReplace.SearchString = "Old text" oReplace.ReplaceString = "New text" oSheet.ReplaceAll (oReplace)
.
StarBasic is no Visual Basic. There are subtle differences. Try this VB code: Sub Main() Dim oServiceManager As Object Set oServiceManager = CreateObject("com.sun.star.ServiceManager") Dim oDesktop As Object Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop") 'Open a new document based on a template. Dim sTemplatePath As String Dim oDocument As Object Dim oSheet As Object oTemplatePath = "file:///D:/Example.stc" ' args was not declared Dim args() As Object 'typo: you used sTemplatePath Set oDocument = oDesktop.loadComponentFromURL(oTemplatePath, "_blank", 0, args) ' see comments after code Set oSheets = oDocument.getSheets Set oSheet = oSheets.getByIndex(0) If oSheet.supportsService("com.sun.star.util.createReplaceDescriptor") Then MsgBox "The service is supported." 'No msgbox is shown. End If 'Modify document content Dim oReplace As Object Set oReplace = oSheet.createReplaceDescriptor 'Fails! 'Set must not be used for assigning strings oReplace.SearchString = "Old text" oReplace.ReplaceString = "New text" ' When calling a function and discarding the return value you must not use brakets or you must use "call" oSheet.ReplaceAll oReplace End Sub When writing VB code please always look at the idl descriptions of the interfaces. Also read about how this idl stuff is mapped to the coresponding Automation types at http://api.openoffice.org/docs/DevelopersGuide/ProfUNO/ProfUNO.htm#1+4+UNO+Language+Bindings chapter 3.4.4 Automation Bridge To get the collection of sheets you need to call XSpreadsheetDocument.getSheets see http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XSpreadsheetDocument.html The returned object supports the com::sun::star::container::XIndexAccess on which the function getByIndex can be called. StarBasic knows about these relations but VB does not. To find out what interfaces are implemented you can use the dbg_supportedInterfaces property in StarBasic: msgbox oDocument.dbg_supportedInterfaces This will open a message box containing all supported interfaces.
OK. Thank you.