Insert cells in OpenOffice Calc from VB 1

[peterd51]

Hi,

I'm looking at Open Office (v4.1.5) and accessing Calc (=Excel) from VB. Specifically VB5 but I hope I can convert any suggestions made for VB.net, etc.

I've played around with a few things for a few days, checked online, nothing seems to work.

  Dim URL As String
  Dim oSM As Object             'Root object for accessing OpenOffice from VB
  Dim oDesk As Object           'Initial set up
  Dim oDoc As Object            'initial set up
  Dim OpenParam(1) As Object    'Parameters to open the doc

  Dim oSheets As Object
  Dim oSheet As Object
  Dim oRange As Object
  Dim oCRAddr As Object		
  Dim oCell As Object

  Set oSM = CreateObject("com.sun.star.ServiceManager")			'initial set-up
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")

'Open Doc____________________________________________________ 
  URL = "file:///D:/Open%20Office/test%201.csv"	
  Set oDoc = oDesk.loadComponentFromURL(URL, "_blank", 0, OpenParam)	'Open the Doc

  Set oSheets = oDoc.getSheets()					'get available WorkSheets
  Set oSheet = oDoc.sheets(0)						'point to 1st WorkSheet

'Cell Range for Insert________________________________________ 
  Set oCRAddr = oSM.Bridge_GetStruct("com.sun.star.table.CellRangeAddress") 'Set Cell Range
  
  oCRAddr.sheet = 0
  oCRAddr.StartColumn = 1
  oCRAddr.StartRow = 1
  oCRAddr.EndColumn = 2
  oCRAddr.EndRow = 2
  
'OK to here........................

'fails below on "com.sun.star.sheet.CellInsertMode.DOWN"
  Call oSheet.insertCells(oCRAddr, "com.sun.star.sheet.CellInsertMode.DOWN")

'gives error 438  Object doesn't support this property or method

does anyone know how to 'Dim' and 'set' anything that can hold "com.sun.star.sheet.CellInsertMode.DOWN" so I can pass it to the Open Office Calc worksheet?

I've tried ("com.sun.star.sheet.CellInsertMode.DOWN"), still fails.

Regards
Peter

 

[strongm]

Strikes me that this is more about OpenOffice automation than it is about VB5 or 6. Might be better off trying in forum904

 

[peterd51]

Hi strongm,

thanks, I've tried there but they're not familiar with VB.

The problem is how to set something in VB to hold "com.sun.star.sheet.CellInsertMode.DOWN".

If I try it as an object then it rejects on 'com.'.

As a String it isn't passed to OPenOffice.

I've poked around with Property but that doesn't help.

Is there anything else I could try?

Regards
Peter

 

[strongm]

Nevertheless, it is an OpenOffice COM automation issue, not VB (the issue would affect any language that supports COM automation - VB, VBA, JavaScript, etc).

The problem, as far as I am aware, is that OpenOffice enumerations are not directly accessible via COM, even through the ServiceManager. So you have to make local equivalents.

So try

[tt]

[blue]Private Enum CellInsertMode
    None
    Down
    Right
    Rows
    Columns
End Enum[/blue]

[/tt]
which should mean that you can now change your call to

[tt]

[blue]Call oSheet.insertCells(oCRAddr, CellInsertMode.Down)[/blue]

[/tt]

 

[peterd51]

Hi strongm

I now have:

(General)(Declarations)
Option Explicit

Private Enum CellInsertMode
    none
    DOWN
    Right
    rows
    Columns
End Enum

Private Sub Form_Load()
...

Call oSheet.insertCells(oCRAddr, CellInsertMode.DOWN)

but this still gives error 438 'Object doesn't support this property or method'.

In the 'immediate window' I can 'print CellInsertMode.Down' and it reports as "1", as expected.
I can also print oCRAddr.sheet, oCRAddr.StartRow, etc, all seems OK.

Any idea why I Still get the error please?

Regards
Peter

 

[strongm]

Ys, because you are not using the right object ...

[tt][blue]Set oSheet = oDoc.sheets(0)[/blue][/tt]

is not getting the correct object. Try

[tt][blue]Set oSheet = oDoc.CurrentController.ActiveSheet[/blue][/tt]

 

[peterd51]

Hi strongm,

that's got it! Thanks!

Regards
Peter

 

[Andrzejek]

To show appreciation of help received, it is customary on TT to award a star for helpful poet(s). Click [blue]Great Post![/blue] link on helpful post(s)


---- Andy

There is a great need for a sarcasm font.

 

[peterd51]

Hi Andrzejek,

I did.

But yesterday I had problems logging in and had to do it three times before I could post my reply, so it may have failed while I was on an earlier attempt.

I've just done it again...not sure if it's worked or not yet.

Regards
Peter

 

[strongm]

Psst! In the openoffice forum you cross-posted this in you seem to be saying that you are using

[blue]Set oSheets = oDoc.getSheets()
Set oSheet = oDoc.sheets(0)
Set oSheet = oDoc.CurrentController.ActiveSheet     [green]'select sheet 'inside' VB[/green][/blue]

Note that the first two lines of that are no longer necessary. Also, not quite sure what the comment you added means.

You should also ignore the comments that the " the brige is broken and cannot be trusted"; it is nothing to do with case (and both the statements referred to work). The issue is related to which interface (of multiple ones) is returned. GetSheets (and Sheets) return to VB an interface to a lightweight generic object that doesn't support most of the functionality you require (hence the errors you saw). The Controller (in this case the active controller) returns an extended interface that does. See

http://www.openoffice.org/api/docs/common/ref/com/sun/star/frame/XComponentLoader.html

for the OpenOffice documentation.

And VB intellisense (autocase and command completion, for example) doesn't work with latebound objects (which all the OpenOffice objects are), nor can you inspect the objects in the object browser or the Locals window. This is expected behaviour, not an error.

 

[peterd51]

Hi Strongm,

yes, I cross-posted because I think it's best to approach these problems from both sides.

The guys over there seem to have problems with VB. I accept that they don't know it, but they query things like 'object' telling me it's something else on their side. As long as it works I don't worry about what it's called.

'Lupp' asked about case sensitivity and inspecting objects so I explained it as best I could.

I've looked at the API Reference a few times and it's starting to make some sense to me on the command structure but it's a bit too formal for me to fully understand it. I'm not a programmer and I would never suggest that I ever was or will be.

In the past there's always been lots of VB code around for me to scan through and pick out the bits that I need, not so much now and especially for using VB to access OOo as VB seems to be dying out.

Thanks for pointing out the first two lines (the code still runs OK with them in) but I haven't had time to play around and see what I can remove yet, I've been unwell for a couple of days, a lot better this morning.

Over the next few weeks I hope to build my own reference of code that does different things, I guess around a dozen blocks or so, and I'll use that for everything I'll neeed afterwards.

Regards
Peter