Page 1 of 1
[Solved] Trying to center a value in a cell
Posted: Wed Dec 30, 2009 1:16 am
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,
Re: Trying to center a value in a cell
Posted: Wed Dec 30, 2009 3:29 am
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
Re: Trying to center a value in a cell
Posted: Wed Dec 30, 2009 8:33 pm
by thepla
I had that one before and I get error
Name 'com' is not declared.
I tried again and get the same results.
Re: Trying to center a value in a cell
Posted: Thu Dec 31, 2009 1:46 am
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
Re: Trying to center a value in a cell
Posted: Thu Dec 31, 2009 7:23 am
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,
Re: Trying to center a value in a cell
Posted: Thu Dec 31, 2009 12:49 pm
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.
Re: Trying to center a value in a cell
Posted: Thu Dec 31, 2009 5:07 pm
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.
Re: Trying to center a value in a cell
Posted: Thu Dec 31, 2009 6:06 pm
by turtle47
thepla wrote:and does not center
Do you want to try another code?
Re: Trying to center a value in a cell
Posted: Fri Jan 01, 2010 2:17 am
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,
Re: [Solved] Trying to center a value in a cell
Posted: Thu Mar 30, 2017 5:27 pm
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
Re: [Solved] Trying to center a value in a cell
Posted: Thu Mar 30, 2017 9:55 pm
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
Re: [Solved] Trying to center a value in a cell
Posted: Thu Mar 30, 2017 10:15 pm
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?
Re: [Solved] Trying to center a value in a cell
Posted: Thu Mar 30, 2017 11:53 pm
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.
Re: [Solved] Trying to center a value in a cell
Posted: Fri Mar 31, 2017 10:28 am
by pansmanse
Thanks, FJCC.
Ah, the mysteries of Basic!