[Solved] Trying to center a value in a cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
thepla
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

[Solved] Trying to center a value in a cell

Post by thepla »

Here is code with several tries.

ActiveSheet.getCellbyposition(n, 0).CellBackColor = RGB(255, 204, 153)


ActiveSheet.getCellbyposition(n, 0).VertJustify.CENTER()
'ActiveSheet.getCellbyposition(n, 0).VertJustify("com.sun.star.table.CellVertJustify.CENTER")
'ActiveSheet.getCellbyposition(n, 0).VertJustify = 2

The first line to change color works. I included that to hopefully show I'm pointing the correct place.

If I follow examples in documentation I get an error if com is not inside of quotes before even attempting to execute.

The only one that does not give a runtime error is the last one with 2 as value; it did not effect the results.

THanks in advance,
Last edited by Hagar Delest on Sat Jan 02, 2010 10:51 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 3.1 and Visual Basic 2008
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Post by FJCC »

This works for me:

Code: Select all

oDoc = ThisComponent
Sheet = oDoc.Sheets.getByIndex(0)
Cell = Sheet.getCellByPosition(0,0)
Cell.VertJustify = com.sun.star.table.CellVertJustify.CENTER
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
thepla
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Post by thepla »

I had that one before and I get error

Name 'com' is not declared.

I tried again and get the same results.
Open Office 3.1 and Visual Basic 2008
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Post by FJCC »

I see that the defined constants of the OOo API are not available in VB. I used this VB code to open the file Test.ods and set the vertJustify of cell A1of the first sheet to CENTER

Code: Select all

  Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the first and most important service
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")

  Set oDoc = oDesk.loadComponentFromURL("file:///c:/Test.ods", "_blank", 0, arg())

  Set Shts = oDoc.Sheets
  Set Sht = Shts.getByIndex(0)
  Set Cell = Sht.getCellByPosition(0, 0)
  Cell.VertJustify = 2
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
thepla
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Post by thepla »

I am not by my working computer.

it looks like your example matches the one I have above that did actually process without error. My issue was a value of 2 did not actually center. Did yours center the value of "A1"?

Thanks,
Open Office 3.1 and Visual Basic 2008
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Post by FJCC »

Yes, my code centers the text in the cell, and if I change the value of vertJustify to 1, the text is placed at the top of the cell. I wonder if ActiveSheet is really the object you expect it to be. I suggest replicating my version by creating a test document and using my exact code. If that works, you'll know that it can be done.
I'll be traveling the next few days, so it is likely I will not be on line until Monday.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
thepla
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Post by thepla »

myDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, dummyArray)

Dim Shts As Object
Dim Sht As Object
Dim Cell As Object

Shts = myDoc.SHeets
Sht = Shts.getbyindex(0)
Sht.getCellbyposition(0, 0).setstring("SKU/SCAN")

Cell = Sht.getcellbyposition(0, 0)
Cell.vertjustify = 2


This opens up a spreadsheet and puts "SKU/SCAN" into cell A1 and does not center.
Open Office 3.1 and Visual Basic 2008
turtle47
Posts: 31
Joined: Tue Sep 16, 2008 3:54 pm

Re: Trying to center a value in a cell

Post by turtle47 »

thepla wrote:and does not center
Do you want to try another code?

Code: Select all

Cell.HoriJustify = 2
OOo 3.2.X on Ms Windows 7
thepla
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Post by thepla »

Don't know if I should call you a genius, me a fool, or wonder if there is something wrong.

I guess if Vertical is up and down I am a fool. Horizontal did center left to right.

Thanks a ton,
Open Office 3.1 and Visual Basic 2008
pansmanse
Posts: 29
Joined: Wed Nov 11, 2009 4:30 pm

Re: [Solved] Trying to center a value in a cell

Post by pansmanse »

I realise that this is a very old post, but if anyone is still listening ...
Does this work in Writer text tables as well as Calc? Because I can't make vertical justify work. I've tried these, but no joy.

oCell.VertJustify = 2
oCell.VertJustify = com.sun.star.table.CellVertJustify.BOTTOM
Pansmanser
LO3.5.7.2/Ubuntu12.04
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Trying to center a value in a cell

Post by FJCC »

This works for me

Code: Select all

oTables = ThisComponent.TextTables
oTbl = oTables.getByName("Table1")
oCell = oTbl.getCellByName("A1")
oCell.VertOrient = com.sun.star.text.VertOrientation.CENTER
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
pansmanse
Posts: 29
Joined: Wed Nov 11, 2009 4:30 pm

Re: [Solved] Trying to center a value in a cell

Post by pansmanse »

Thanks for the prompt reply, FJCC. I believe my problem was that I was using a cell range rather than a single cell. This now works, but seems cumbersome.

oCell = oTable.getCellRangeByName("A1:A1") 'has to be range to set font size?
oCell.CharHeight = 12
oCell = oTable.getCellByName("A1")
oCell.VertOrient = 3

The reason for using a range is that it doesn't seem to be possible to set CharHeight for a single cell, but only a range. Off topic now, but any ideas?
Pansmanser
LO3.5.7.2/Ubuntu12.04
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Trying to center a value in a cell

Post by FJCC »

I confirmed that getting a single cell does not allow changing CharHeight but getting a range spanning one cell does allow it. I can't think of a way around that other than your solution or something very similar.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
pansmanse
Posts: 29
Joined: Wed Nov 11, 2009 4:30 pm

Re: [Solved] Trying to center a value in a cell

Post by pansmanse »

Thanks, FJCC.
Ah, the mysteries of Basic!
Pansmanser
LO3.5.7.2/Ubuntu12.04
Post Reply