VB5 Set Calc cell to 2 decimal places

[Peterd51]

Hi,

I can do most things now but I'm stuck on setting the number of decimal places in a cell.

Looking around I've found odd bits of code and I've played around with it, end up with the code below.

I read somewhere that if oLoc isn't given then it uses default settings?

I get a '440 Automation error' on Set oLoc = oSM.Bridge_GetStruct, and if I rem it out I get the '438' error on the last line.

Code: Select all

'initial set up OK to here

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

'OK to here, sheet opens and the cell is set to 1000

  Dim oNumberFormats As Object
  Dim NumberFormatString As String
  Dim NumberFormatId As Long

  Dim oLoc As Object 
  'API says it's a 'struct' but I'm not sure if this line is correct
  'Set oLoc = oSM.Bridge_GetStruct("com.sun.star.lang.locale")  '440 Automation error
 
  'Set oLoc.language = "en"
  'Set oLoc.Country = "gb"
 
  Set oNumberFormats = oDoc.NumberFormats
  NumberFormatString = "#,##0.00"
 
  NumberFormatId = oNumberFormats.queryKey(NumberFormatString, oLoc, True)
  If NumberFormatId = -1 Then
    NumberFormatId = oNumberFormats.AddNew(NumberFormatString, oLoc)
  End If
 
  MsgBox NumberFormatId                   '=106

  Call oCell.NumberFormats(NumberFormatId) 'error 438 Object doesn't support this property or method

Any suggestions please?

Regards
Peter

[Villeroy]

Simply use a custom cell style.

Code: Select all

objCell.CellStyle = "My_sophisticated_style_with_dozends_of_attributes"

[Peterd51]

Hi Villeroy:

A bit cryptic but I'll poke around with that for a day or two.

Thanks
Peter

[RoryOF]

Note that OpenOffice BASIC has it own idiosyncrasies; in outline it is similar to most BASICs, but the calls to the OO engine (the OO API) are unlike those used in VB and can involve detailed study. See Andrew Pitonyak's books, downloadable from his site at http://www.pitonyak.org/oo.php
for detailed help.

However, it is possible that the OpenOffice BASIC manual at
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide
will be more than adequate.

[Villeroy]

A programmer can use an office suite just like a professional office worker saving thousands of lines of codes. Prepare your templates with styles, layouts, fields, named elements, headers, footers and everything. And if you still need some silly Basic code, just refer to the well prepared document properties.
Applying hard formatting to office documents ignores the major capability of the software product. All office suites save time and effort by means of templates and styles. Doing these mistakes programmatically is a waste of development time.

[Peterd51]

Hi RoryOF,

thanks for the links, I've already got copies of both and I have been reading them, taking blocks of code and poking around with it. The API Reference has helped some times where the code I got had 'get' or 'set' missing from the middle.

There's just odd bits that VB expects that gives me problems.

Villeroy: thanks, I'm compiling a list of around a dozen blocks of code that I'll use forever, whatever I can get to work.

As I'm not a programmer the only thing I'm thinking about is to get it working, then I'll just use it over and over again.

Personal use each day, no hurry to get the data so five minutes or ten minutes doesn't matter much.

My VB program gathers the data and pushes it around a few Arrays, then opens Excel and pushes it into sheets and cells.

One program creates a .CSV that gets opened in Excel later. I just need to replace Excel with Calc, move the odd bit of data between sheets but mostly change the background or font colours of some cells, depending on the value / text that they hold.

Almost there now, just poking around with a search routine, although I can select each cell in a column in turn and check against a string if I need to, it'd be a 'nice to have' if I can search and return a cell row / column.

Regards
Peter

[Villeroy]

As I'm not a programmer the only thing I'm thinking about is to get it working, then I'll just use it over and over again.

Spreadsheets are simplified programming languages for non-programmers. This office suite, just like many similar products, is made for people who can not program. It makes no sense to format cells programmatically. Either you read some books on programming (VB is a dead language nowadays) or you read some book on how to use an office suite. These books on Word, Excel & Co were best-sellers in every corner store of the 90ies and they still apply to todays office suites.

[Peterd51]

Hi Villeroy,

sorry but I think you missed my point.

I've been writing basic since 1982 but I would never call myself a programmer as I don't work to a timescale, to anyone elses priorities or within a team.

I do my own thing and no-one else should ever have to worry about the code that I create as its for personal use.

From time to time I've written programs that I used at work (voice / data / security network engineer) but again there was no pressure on me to do it, it was still just a hobby.

I create the spreadsheets programatically as I get data from various sources and I want to display it in an easy to read format and make minor changes to it as different data gets added.

Things like changing background colours to highlight complimentary data. The more 'yellow' I see on a line the more important that data is to me.

Regards
Peter

[Villeroy]

https://wiki.openoffice.org/wiki/Docume ... er_Formats
[Tutorial] Introduction into object inspection with MRI

[Peterd51]

Hi Villeroy,

thanks for the links.

I've looked at MRI a couple of times but I can't see how it can help me.

Regards
Peter

[Villeroy]

Hi Villeroy,

thanks for the links.

I've looked at MRI a couple of times but I can't see how it can help me.

Regards
Peter

This is why I linked to an introductive tutorial.
I can't hardly remember how I could write UNO code without MRI or its predecessor XRay.
Not using templates and styles is like constantly working around the core features of an office suite.

[Peterd51]

Hi Villeroy,

yes, thanks for your help, I read it, but it still doesn't show me how I can use it for what I'm doing.

This is a problem that I've always had with documentation.

I've learnt to install and maintain complex harware systems (phones and network, etc) by getting my hands on the kit and poking around with it rather than reading the books.

In the past I've done what I need in VB by finding code, figuring out what it does and then tweaking it to do what I want it to, and I can do that with OOo basic & macros. I don't have a problem with that side of things.

My problem is there's so little code around for VB to access OOo that I have nothing to play with. As people here have suggested, VB is dying out as Microsoft abandoned it some years ago.

I worked out how to put data into a Calc cell and get it out again, change a cell background colour or font, etc, by poke and hope, but most things don't work as I can't find the specific command to get past the API.

I'll get there eventually, there's no hurry.

Regards
Peter