[Solved] A macro to access an embedded text document

[martius]

Let's assume we have an OO Calc spreadsheet with an embedded text document (I did this: Insert->Object->OLE object->Text). Now I need a macro to access this embedded text document and select the text and type something, programatically.
Anyone know a macro to do this?

Thanks!!!!

[FJCC]

I can access the embedded document on Sheet1 like this. In this example, the embedded document is the only object on Sheet1, which makes it easier to find.

Code: Select all

  oDrawPages = ThisComponent.getDrawPages
  oDrawPage = oDrawPages.getByIndex(0) 'Get the DrawPage of the leftmost sheet
  oOLE_Obj = oDrawPage.getByIndex(0)  'only getByIndex is available. Either know the index or loop through all objects 
  oEmbeddedObject = oOLE_Obj.EmbeddedObject
  oComponent = oEmbeddedObject.Component  'oComponent is the Text document
  oText = oComponent.Text 'oText is just like the Text object of a Writer document
  oText.setString("New text")  

[martius]

Thank you very much, FJCC. Your macro works really nice!!!

Now, how can I loop through all objects and find by the name of the text document, If I don't Know the index?
Thanks!!!

[FJCC]

I don't know if this will work in every case.

Code: Select all

  oDrawPages = ThisComponent.getDrawPages
  oDrawPage = oDrawPages.getByIndex(0) 'Get the DrawPage of the leftmost sheet
  For i = 0 to oDrawPage.Count - 1
  	oObj = oDrawPage.getByIndex(i) 
  	If oObj.supportsService("com.sun.star.drawing.OLE2Shape") then 'test if we have an OLE object
  		oEmbeddedObject = oObj.EmbeddedObject
  		oComponent = oEmbeddedObject.Component  
  		If oComponent.supportsService("com.sun.star.text.TextDocument") then 'checks if we have a Text document
  			If oComponent.NameSpace = "file:///C:/Documents%20and%20Settings/username/My%20Documents/Testdoc.odt/" then 'test for desired document
  				oText = oComponent.Text 'oText is just like the Text object of a Writer document
  				oText.setString("New text3")
  			end if 
  		end if
  	end if  
  next i

[martius]

The problem is that I didn't insert the text document from a file. I don't have a URL to search.

[FJCC]

If you only have one text document embedded, you can remove the innermost If-Then (If oComponent.NameSpace...) and the code will still find the right object. If you have multiple text documents, then I don't see a solution at the moment.

[MrProgrammer]

How can I … find by the name of the text document? I didn't insert the text document from a file.

I'm not sure what "name" you intend to use in this case. If you have multiple embedded non-file text objects, you could right-click them and use Name … to assign names to them. Then in your macro you would use oOLE_Obj.getName() to access the names you assigned.

[martius]

Thank you, guys! I put together what MrProgrammer and FJCC said and could
get this macro below. It's exactly what I wanted!

Thanks a lot for your colaboration!!!

Code: Select all

Sub Main
'I assigned a name to an embedded non-file text object
' by right-click it and used Name...

Dim oOleObjects, oOleObject, oMyOleObject, oMyTextDoc as object

oOleObjects = ThisComponent.DrawPages.getByIndex(0)'Let's assume the DrawPage of the leftmost sheet

	 'Let's look for the object name we want (in this case: teste0)
	 
	 For i = 0 To oOleObjects.Count - 1
	 	oOleObject = oOleObjects.getByIndex(i)
	 	    If oOleObject.Name = "teste0" Then
		       oMyOleObject = oOleObject
		    End If
	 Next
 
	 oMyTextDoc = oMyOleObject.EmbeddedObject.Component
	 
	 oMyTextDoc.Text.setString("EUREKA!!!")
	 
End sub