I would like to set column widths using a Macro in Calc

calc
macro

asked 2019-12-16 00:52:25 +0100

TonyRadice
11 ●1 ●1 ●2

Ladies and Gentlemen - I am a newbie to Libra Office Macro Programming. I have been attempting to find a concise guide to learning this skill and have downloaded a number of documents that are (1) not really answering my questions and (2) seem to be targeted toward older revisions of the application. I am simply left in the dust by the developers guide. I will continue to try to learn from these documents, but I would like to ask for someone to provide me a simple Calc Macro to set multiple column widths in Libra Calc v.6.0.2. I am looking for an example to not only expand upon it, but also “bootstrap” my learning. I would also ask the community for pointers (links) to guides that may help me along the learning path. I would especially like to know HOW does one determine all the properties or methods that are associated with a sheet or document? (A Common error I am getting right now is “Property or Method Not Found”) Do documents exist that list all these characteristics and/or give examples of how they are used? Thank you for your time and any guidance you may provide.

edit retag flag offensive close merge delete

add a comment

4 Answers

Sort by » oldest newest most voted

answered 2019-12-16 10:47:09 +0100

Lupp
21104 ●46 ●127 ●246

updated 2019-12-16 10:49:22 +0100

Code:

Function setColWidth(pS as Long, pC As Long, pWidthBy_cm As Double)
REM pWidthBy_cm must be given using the unit cm!
REM pS for sheetnumber and pC for columnnumber are 1-based 
REM as Calc sheets handle it.
REM the 0-based indexing of te API requires suntraction of 1 therefore.
setcolwidth = "fail"
sheet = ThisComponent.Sheets(pS - 1)
col   = sheet.Columns(pC - 1)
newCW = cInt(pWidthBy_cm * 1000 + 0.5)
REM Mandatory unit used by the API here is 1/100 mm.
col.Width = newCW
If Abs(col.Width - newCW)<=2 Then setColWidth = "done"
End Function

Usage of the function is demonstrated in this example. It may also be colled from another user routine, of course.

edit flag offensive delete link

add a comment

answered 2019-12-16 01:46:59 +0100

Ratslinger
26481 ●9 ●35 ●135

updated 2019-12-16 01:51:58 +0100

Hello,

First, here is a snippet to set the width of column B on Sheet1:

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oColumns As Variant
  Dim oObj2 As Variant
  Dim nWidth As Long

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  oColumns = oObj1.getColumns()

  oObj2 = oColumns.getByName("B")
  nWidth = oObj2.Width
  oObj2.Width = 4000

End Sub

Width is in 1/100th mm -> see TableColumn Service Reference

Now this was actually generated by using MRI - an object inspection tool. For this and other information see ->To learn LibreOffice Base are there introductions or tutorials?.

Yes the title specifies Base but the links in the answer apply to all of LO.

Object inspection tools under "Useful Extensions" (at bottom of answer click on "more" to reveal this) and under "Independent Documentation" is the best single source document for OO/LO macros - Open Office Macros Explained.

Note: There is no single source for everything. For Properties and Methods the combination of OOME and an Object Inspector are invaluable.

This should get you started.

edit flag offensive delete link

Comments

hi @Ratslinger, compendious

   Sub Snippet
     ThisComponent.getSheets().getByName("Sheet1").getColumns().getByName("B").Width = 4000
    End Sub

Schiavinatto ( 2019-12-16 13:31:08 +0100 )edit

@Schiavinatto,

Yes this is known. I believe you miss the point of the question and also why the code was posted as such in my answer - MRI generated. Trying to help someone new to macros.

Ratslinger ( 2019-12-17 03:11:07 +0100 )edit

add a comment

answered 2019-12-19 16:10:15 +0100

TonyRadice
11 ●1 ●1 ●2

updated 2019-12-19 16:49:22 +0100

Ladies and Gentlemen - Thank you very much for your answers! The function call above was precisely what I needed to execute the task (it works!!) and I will be looking up the references provided next. Again - MANY THANKS!!

One further note - As I am using Libra Office 6.0.2.1 (x64) It does not seem a version of MRI is available? Am I missing an obvious answer or is there a different source? The versions that MRI seem to support are only up to version 3.5.

edit flag offensive delete link

Comments

Please do not use the Answer field for comments that are not an answer to the original question, use add a comment instead. Thanks.

And as you may realize now, "the function call above" is meaningless as your "answer" is displayed on top as the newest answer.. instead, click the check mark icon to mark the answer as correct.

erAck ( 2019-12-19 16:41:30 +0100 )edit

One further note

Nice! Besides posting a comment as "solution" - which is not a solution - now you decided to break another rule of the site: different topics should be posted in separate questions.

As I am using Libra Office 6.0.2.1 (x64) It does not seem a version of MRI is available? Am I missing an obvious answer or is there a different source? The versions that MRI seem to support are only up to version 3.5.

... and here others need to unpack their magic crystals and guess that you meant the LibreOffice extensions site, with MRI page there; and that you didn't look there to see links to external project page / homepage of the project.

Mike Kaganski ( 2019-12-19 16:56:01 +0100 )edit

add a comment

answered 2019-12-16 08:57:53 +0100

morphingstar
11 ●1 ●4 ●7

Probably waste of time, I asked OO years ago, the received macro did work for one set of col.width. If you wanted to change it the macro needed to be changed. OO and LO can not read a cell content and apply the number to the cell width. If you record a cell row containing the numbers the resulting macro will not read it again it just uses the numbers stored inside the macro. New cell content is ignored.

Auto-ranging a table is unfortunately not possible with LO OO, your need ATARI 1000 spread sheet software to create this convenience. About year 1985. The same is true for auto-column width creation - both without (non-working OO LO) hand knitted macros.

Auto-ranging means recording a macro which will find out the data range: A table or a horizontal list of column widths. Simply recording, no programming! Then type-adapt range data without macro / programming. Modern and complicated or impossible versus old, simple/ and no longer available.

edit flag offensive delete link

Comments

User code for LibO / AOO can read cell contents, of course. There is, however, the general problem of how to pass parameters to the routines. If you record a "macro" you never get recorded the way of obtaining a value, but the value (may be a cell address or whatever) only. This is due to the fact that the macro recorder is not and cannot be an "interaction logger".
To get flexible user routines you basically need to solve the problem of passing parameter values. Depending on the use case there are a few ways to get it done. In Calc specifically you can read parameter values from dedicated CellRange e.g. There are alternatives depending on the context.
The most simple way to write the user code as a function callable via a cell formula, is restricted for urgent reasons. It works in the case under discussion, but may ...(more)

Lupp ( 2019-12-16 10:43:55 +0100 )edit

add a comment

I would like to set column widths using a Macro in Calc

4 Answers

Comments

Comments

Comments

Question Tools

Stats

Related questions

I would like to set column widths using a Macro in Calc edit

4 Answers

Comments

Comments

Comments

Question Tools

Stats

Related questions

I would like to set column widths using a Macro in Calc