VB5 to OOo Calc set decimal places in cell

[peterd51]

Hi,

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:

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?

Regards
Peter

 

[strongm]

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

 

[peterd51]

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.

Regards
Peter

 

[strongm]

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

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

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

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

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

    Set oSheet = oDoc.CurrentController.ActiveSheet [green]' Get the OpenOffice object representing the activbv spreadsheet[/green]

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

End Sub[/blue]

 

[peterd51]

Hi Strongm,

thanks for the code.