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.
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.
hi @Ratslinger, compendious
Sub Snippet
ThisComponent.getSheets().getByName("Sheet1").getColumns().getByName("B").Width = 4000
End SubYes 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.
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.
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.
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 fail in other cases.
Also see my answer.
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.
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.
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.