[Solved] How do I get the NUMBERFORMATS object in VB6

Discuss the spreadsheet application

[Solved] How do I get the NUMBERFORMATS object in VB6

Postby LBorlase » Mon May 22, 2017 3:26 am

Hello
I have a routine in VB6 that populates a Grid into a Calc spreadsheet - Code below.
I cannot figure out how to populate the NumberFormats object (o??????).
Any Ideas?

Code: Select all   Expand viewCollapse view
  Dim oServiceManager As Object
  Dim oDesktop        As Object
  Dim oDocument       As Object
  Dim oSheet          As Object
  Dim oWindow         As Object
  Dim oFrame          As Object
  Dim oLocale         As Object
  Dim oNumberFormats As Object

  Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
  Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
  ' Create a new spreadsheet document.
  Set oDocument = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array())
  Set oFrame = oDesktop.ActiveFrame
  Set oWindow = oFrame.GetContainerWindow
 
  Set oLocale = oServiceManager.Bridge_GetStruct("com.sun.star.lang.Locale")
  oDocument.getCurrentController.getFrame.GetContainerWindow.SetVisible False

  oNumberFormats = o???????.NumberFormats    [color=#808000]This is where I need to populate the oNumbeFormats object[/color]

  ' Get Spreadsheets Object
  Set oSheet = oDocument.Sheets(0).getByIndex(0)
......


Thank you

Laurie Borlase
Last edited by LBorlase on Mon May 22, 2017 4:53 pm, edited 2 times in total.
Open Office 4.1.3
Windows 7
LBorlase
 
Posts: 3
Joined: Mon May 22, 2017 3:06 am

Re: How do I get the NUMBERFORMATS object in VB6

Postby FJCC » Mon May 22, 2017 4:39 am

Here is a bit of OpenOffice Basic code I happen to have that creates an unusual number format and applies it to a cell. The oDoc object in my code corresponds to oDocument in your code.
Code: Select all   Expand viewCollapse view
Dim sLocale as New com.sun.star.lang.Locale
dateFormatString = "YYYY/MM/DD\\ HH:MM:SS"
oDoc = ThisComponent
NumForms = oDoc.getNumberFormats()
DateKey = NumForms.queryKey(dateFormatString, sLocale, True)
Print DateKey
IF DateKey = -1 then
   DateKey = NumForms.addNew(dateFormatString, sLocale)
   Print DateKey
end if
Sheet = oDoc.Sheets.getByIndex(0)
Cell = Sheet.getCellByPosition(0,0)
Cell.NumberFormat = DateKey
Cell.Value = 40000
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7056
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How do I get the NUMBERFORMATS object in VB6

Postby Zizi64 » Mon May 22, 2017 6:45 am

Do you want to format cells by direct formatting?
If you are using the native, Inrenational Standard ODF fileformats, then is is better to use the Cell styles instead of the applying some direct formatting by macro codes. You can create, apply and modify user defined cell styles by your macro.

Examples:
Create or modify a style - https://forum.openoffice.org/en/forum/v ... hp?t=48960
Apply a style - http://ooo-forums.apache.org/en/forum/v ... 20&t=67938
Get the style of a cell/cellrange by various referencing - https://forum.openoffice.org/en/forum/v ... =21&t=2762

Here is the desctription of the API function of the NumberFormats:
https://www.openoffice.org/api/docs/com ... rmats.html

The numberformat constants: http://api.libreoffice.org/docs/idl/ref ... ormat.html

Code example for Numberformat: https://wiki.openoffice.org/wiki/Docume ... _Documents
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How do I get the NUMBERFORMATS object in VB6

Postby LBorlase » Mon May 22, 2017 4:08 pm

Thank you for your replies.

In all of the examples the line "DOC = ThisComponent" is in the code.

What is ThisComponent or how do I define it?

Thank you
Open Office 4.1.3
Windows 7
LBorlase
 
Posts: 3
Joined: Mon May 22, 2017 3:06 am

Re: How do I get the NUMBERFORMATS object in VB6

Postby Zizi64 » Mon May 22, 2017 4:33 pm

ThisComponent


In most of cases it means (references to) the active document:
(the document what you can see on the display / the document that you edit in present time / the document where the macro was launched from)

You need study the API functions. Start with Andrew Pitonyak's free books.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How do I get the NUMBERFORMATS object in VB6

Postby Zizi64 » Mon May 22, 2017 4:46 pm

In the object oriented programming environments and languages you need define (get) the objects.

Code: Select all   Expand viewCollapse view
Dim oDoc as object
oDoc = ThisComponent


oDoc: it is an user defined name of an object type variable. You will reference to the document object with this name in your code. The actual document object has many-many properties, methods...
You can reference to the active document by usage of the 'ThisComponent' API function. Or you can load a document from a specific URL (from the HDD) or you can create a new document by the
Code: Select all   Expand viewCollapse view
oDoc = ... ...loadComponentFromURL("private:factory/scalc", "_self", 0, array())...
API function
Last edited by Zizi64 on Mon May 22, 2017 6:15 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How do I get the NUMBERFORMATS object in VB6

Postby LBorlase » Mon May 22, 2017 4:52 pm

Thank you.

I used the line:

Set oNumberFormats = oDocument.Numberformats

This now works and the formats populate in the spreadsheet..
Open Office 4.1.3
Windows 7
LBorlase
 
Posts: 3
Joined: Mon May 22, 2017 3:06 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 35 guests