Creating a text document from a Calc spreadsheet

users
#1

Hi,

First of all, I'm using libreoffice 5.2.6.

I've made some macros with Excel in my days but never used Calc and never
interfaced with another application (Word or Writer) so I'm pretty lost
here.

I'd like to generate a text document using data from Calc.
Here's an anonymized example of a Calc file: https://ufile.io/yt27b
Here is the end result Id like to generate: https://ufile.io/j3e87

You can see column BCDE are meant to be a sort of paragraph header, F2:M2
are to be included if the cell is ticked, N contains a brief explanation of
the case.

Again, never used Calc for macros so I'll need basic training

Thanks for your help.

#2

Hi,

First of all, I'm using libreoffice 5.2.6.

I've made some macros with Excel in my days but never used Calc and never
interfaced with another application (Word or Writer) so I'm pretty lost
here.

I'd like to generate a text document using data from Calc.
Here's an anonymized example of a Calc file: https://ufile.io/yt27b
Here is the end result Id like to generate: https://ufile.io/j3e87

You can see column BCDE are meant to be a sort of paragraph header, F2:M2
are to be included if the cell is ticked, N contains a brief explanation of
the case.

Again, never used Calc for macros so I'll need basic training

Thanks for your help.

I would start by visiting this page:
http://www.pitonyak.org/oo.php

Kind regards

Johnny Rosenberg

#3

Hi Martini,

that is a task, which should be solved using a database. If this is the only table, you can use the table itself as flat database, otherwise make a database and import the table data.

Write a query to get the value you want.

Make a report to show the result of the query.

There exist a guide for using database with LibreOffice
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook

In Microsoft Office people often do not use a database, because the home and the small business package of MS Office do not include Access and the larger one is much more expensive. But in LibreOffice full database support is integrated :slight_smile:

Kind regards
Regina

Martini schrieb:

#4

Another hint, which you'll also find sooner or later when following the
link I provided, is the Xray tool. It's great for investigating objects.

I went the other way around, I started writing macros in OpenOffice.org
Calc (then LibreOffice Calc, then Apache OpenOffice and now LibreOffice
Calc again) and now I'm also writing macros in Excel at work (I run 100 %
Linux at home so no Excel there). There are some similarities but the
differences are also huge. Some things are easier in Excel and some things
are easier in LibreOffice/Apache OpenOffice.

Here are some really basic examples about working vill cell values:
Option Explicit

Dim MyString As String, Col As Long, Row As Long
Row=3
Col=9
' You can refer to sheets and cells by names or position. the following two
lines can be equivalent:
MyString=ThisComponent.Sheets.getByName("Data").getCellByPosition(Col,Row).getString()
MyString=ThisComponent.Sheets.getByIndex(2).getCellRangeByName("J4").getString()

' The other way around:
ThisComponent.Sheets.getByName("Data").getCellByPosition(Col,Row).setString(MyString)
ThisComponent.Sheets.getByIndex(2).getCellRangeByName("J4").setString(MyString)

Also available are .setFormula(), .setValue(), getFormula() and getValue().

Of course you can make the lines shorted like these examples:

' Example 1
Dim Sheet As Object, Cell As Object
Sheet=ThisComponent.Sheets.getByName("Data")
Cell=Sheet.getCellByPosition(Col,Row)
MyString=Cell.getString()

' Example 2
Dim MyFormula As String
With ThisComponent.Sheets.getByName("Data")
MyString=.getCellByPosition(Col,Row).getString()
MyFormula=.getCellByPosition(Col,Row).getFormula()
End With

Kind regards

Johnny Rosenberg

#5

Hi Martini,

that is a task, which should be solved using a database.

On the other hand, what's the fun of that…? :stuck_out_tongue:

Kind regards

Johnny Rosenberg