[Solved] Trying to center a value in a cell

Creating a macro - Writing a Script - Using the API

[Solved] Trying to center a value in a cell

Postby thepla » Wed Dec 30, 2009 1:16 am

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
thepla
 
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Postby FJCC » Wed Dec 30, 2009 3:29 am

This works for me:
Code: Select all   Expand viewCollapse view
oDoc = ThisComponent
Sheet = oDoc.Sheets.getByIndex(0)
Cell = Sheet.getCellByPosition(0,0)
Cell.VertJustify = com.sun.star.table.CellVertJustify.CENTER
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 5807
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Postby thepla » Wed Dec 30, 2009 8:33 pm

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
thepla
 
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Postby FJCC » Thu Dec 31, 2009 1:46 am

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   Expand viewCollapse view
  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
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 5807
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Postby thepla » Thu Dec 31, 2009 7:23 am

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
thepla
 
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Postby FJCC » Thu Dec 31, 2009 12:49 pm

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 5807
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Trying to center a value in a cell

Postby thepla » Thu Dec 31, 2009 5:07 pm

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
thepla
 
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

Re: Trying to center a value in a cell

Postby turtle47 » Thu Dec 31, 2009 6:06 pm

thepla wrote:and does not center


Do you want to try another code?
Code: Select all   Expand viewCollapse view
Cell.HoriJustify = 2
OOo 3.2.X on Ms Windows 7
turtle47
 
Posts: 31
Joined: Tue Sep 16, 2008 3:54 pm

Re: Trying to center a value in a cell

Postby thepla » Fri Jan 01, 2010 2:17 am

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
thepla
 
Posts: 14
Joined: Thu Dec 24, 2009 9:34 pm

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

Postby pansmanse » Thu Mar 30, 2017 5:27 pm

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
pansmanse
 
Posts: 26
Joined: Wed Nov 11, 2009 4:30 pm

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

Postby FJCC » Thu Mar 30, 2017 9:55 pm

This works for me
Code: Select all   Expand viewCollapse view
oTables = ThisComponent.TextTables
oTbl = oTables.getByName("Table1")
oCell = oTbl.getCellByName("A1")
oCell.VertOrient = com.sun.star.text.VertOrientation.CENTER
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 5807
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby pansmanse » Thu Mar 30, 2017 10:15 pm

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
pansmanse
 
Posts: 26
Joined: Wed Nov 11, 2009 4:30 pm

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

Postby FJCC » Thu Mar 30, 2017 11:53 pm

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 5807
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby pansmanse » Fri Mar 31, 2017 10:28 am

Thanks, FJCC.
Ah, the mysteries of Basic!
Pansmanser
LO3.5.7.2/Ubuntu12.04
pansmanse
 
Posts: 26
Joined: Wed Nov 11, 2009 4:30 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests