Contact US

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.

Students Click Here

VB5 to OOo Calc set decimal places in cell

VB5 to OOo Calc set decimal places in cell

VB5 to OOo Calc set decimal places in cell


I'm trying to set a cell to show numbers to two decimal places and I've found various bits of code around but nothing works for me.

I currently have:

CODE -->

Dim oCell As Object
Set oCell = oSheet.getCellByPosition(1, 1)
Call oCell.setvalue(1000)

Dim NumberFormats As Object
Dim NumberFormatString As Object
Dim FormatVal As Object

Dim Loc As Object
Set Loc = oSM.Bridge_GetStruct("com.sun.star.lang.locale")

Dim FormatID As Long

'Set NumberFormatString = oDoc.GetNumberFormats()
Set NumberFormatString = oDoc.NumberFormats

FormatID = NumberFormats.queryKey(f, Loc, False)

If FormatID = -1 Then
  FormatID = NumberFormats.AddNew(f, Loc)
End If
GetFormat = FormatID

Call oCell.NumberFormatString("#.##0.00") 

some of this is likely redundant, or simply wrong, but as it stands it doesn't throw any errors...

but it doesn't do anything either.

Any suggestions please?


RE: VB5 to OOo Calc set decimal places in cell

Once again, Peter, this is not really a V5/6 question; it is about the OpenOffice automation objects and API.

RE: VB5 to OOo Calc set decimal places in cell

Hi Strongm,

and once again this is something in VB stopping OOo from accepting the commands that I send it as I don't know the correct VB format to use.


RE: VB5 to OOo Calc set decimal places in cell

It is NOT VB stopping you. The problems in your previous posts were also NOT VB stopping you. The issue was you did not know the Open Office automation interfaces, or which bits of it to use. That is not an issue with VB. It isn't about the 'correct VB format' to use. It is the OO automation library that is the issue. I appreciate that, as a technical user, you may not completely see the difference. But the main pointer is that to solve your issues requires referring to the Open Office documentation, not the VB documentation (not quite true - you do need to understand that OpenOffice objects are late bound to understand how to declare them properly in VB - but as it happens this is also documented in the Open Office StarBasic documentation ...)


Private Sub Example()
    Dim URL As String[/green]
    Dim oSM As Object           ' Root object for accessing OpenOffice from VB
    Dim oDesk As Object         ' This will be an OpenOffice object
    Dim oDoc As Object          ' This will be an OpenOffice object
    Dim OpenParam(0) As Variant ' Parameters to open the doc
    Dim Locale As Object        ' OpenOffice Locale structure
    Dim NumberFormats As Object ' OpenOffice number formats
    Dim FormatId As Long        ' OpenOffice index value for a specific number format
    Dim oSheet As Object        ' OpenOffice spreadsheet object
    Dim oCell As Object         ' OpenOffice cell object
    Set oSM = CreateObject("com.sun.star.ServiceManager")         ' Get OpenOffice ServiceManager
    Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")  ' Get OpenOffice Desktop
    Set Locale = oSM.Bridge_GetStruct("com.sun.star.lang.Locale") ' Get OpenOffice Locale structure

    URL = "file:///C:/office2.csv"

    Set oDoc = oDesk.loadComponentFromURL(URL, "_blank", 0, OpenParam)    ' Use OpenOffice object using OpenOffice syntax to open a document

    ' Now use OpenOffice to determine the correct index to use to select the correct number format
    Set NumberFormats = oDoc.NumberFormats ' Get OpenOffice collection (not VB Collection) containing all known number formats for this document
    FormatId = NumberFormats.queryKey("0,000.####", Locale, False) ' Use OpenOffice to query if number format already exists in OpenOffice collection, and if so return its index
    If FormatId = -1 Then ' Doesn't exist, so
        FormatId = NumberFormats.AddNew("0,000.####", Locale) ' Use OpenOffice to add custom format to OpenOffice collection
    End If

    Set oSheet = oDoc.CurrentController.ActiveSheet ' Get the OpenOffice object representing the activbv spreadsheet

    Set oCell = oSheet.getCellByPosition(0, 0) ' Get the OpenOffice cell object
    oCell.setvalue 1000.12345678 ' Use OpenOffice syntax set the value of the OpenOffice cell for purposes of this example
    oCell.NumberFormat = FormatId ' Apply the ID identified by OpenOffice that represents the number format we want, and use OpenOffice syntax to apply it to the OpenOffice cell

End Sub 

RE: VB5 to OOo Calc set decimal places in cell

Hi Strongm,

thanks for the code.

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! Already a Member? Login

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