INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert cells in OpenOffice Calc from VB

Insert cells in OpenOffice Calc from VB

(OP)
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.

CODE

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

RE: Insert cells in OpenOffice Calc from VB

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

RE: Insert cells in OpenOffice Calc from VB

(OP)
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

RE: Insert cells in OpenOffice Calc from VB

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

CODE

Private Enum CellInsertMode
    None
    Down
    Right
    Rows
    Columns
End Enum 

which should mean that you can now change your call to

CODE

Call oSheet.insertCells(oCRAddr, CellInsertMode.Down) 



RE: Insert cells in OpenOffice Calc from VB

(OP)
Hi strongm

I now have:

CODE -->

(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

RE: Insert cells in OpenOffice Calc from VB

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

Set oSheet = oDoc.sheets(0)

is not getting the correct object. Try

Set oSheet = oDoc.CurrentController.ActiveSheet

RE: Insert cells in OpenOffice Calc from VB

(OP)
Hi strongm,

that's got it! Thanks!

Regards
Peter

RE: Insert cells in OpenOffice Calc from VB

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


---- Andy

There is a great need for a sarcasm font.

RE: Insert cells in OpenOffice Calc from VB

(OP)
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

RE: Insert cells in OpenOffice Calc from VB

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

CODE

Set oSheets = oDoc.getSheets()
Set oSheet = oDoc.sheets(0)
Set oSheet = oDoc.CurrentController.ActiveSheet     'select sheet 'inside' VB 

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/... 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.

RE: Insert cells in OpenOffice Calc from VB

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close