how to define print areas by macro? [Range Functions]

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
nicegreetings
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

how to define print areas by macro? [Range Functions]

Post by nicegreetings »

Hi,

I have a spreadsheet wich is multilingual. That means that for example in column A is the text in german, in colum B in english and so on.
I'm now looking for an user friendly way to print the language prefered. I tried to record a macro wich defines the colums wanted as print area,
but that won't work.
The macro contains a row saying: dispatcher.executeDispatch(document, ".uno:AddPrintArea", "", 0, Array())
How should that be written correctly?

Or is there another comfortable way to do that.

Any help would be highly appreciated.

Marco
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Post by Villeroy »

Select the first range (German in column A).
Tools>Scenarios... Give a name, a color, a description.
Copy the next range (English in column B) over the same range.
Tools>Scenarios... Give a name, a color, a description.

Additional notes:
Scenarios are saved in a special type of hidden sheets.
Properties of existing scenarios are accessible through a button on the navigator.
[X] Copy Back: The scenario is still editable, but the changes are not written into the scenario. When you change to another scenario and back to this one, this one will be unchanged.
[X] Protection: If the edit sheet is protected (Tools>Protection>Sheet...) and the cells of the scenario range are unlocked in general (Format>Cells...) then you can lock this particular scenario.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
nicegreetings
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: how to define print areas by macro?

Post by nicegreetings »

Thanks for you quick response.

I tried it, but that's not really what I need, because the user has to change, add and delete content for all languages.

Do you have another idea?

Marco
OOo 2.4.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Post by Villeroy »

So what's wrong with scenarios?
Put a reference language (e.g. English in A) and several second languages as scenarios in B. May be you can put the same scenarios in two independent scenario ranges, so you can pick any reference language and any other second language. I never tried such a thing. By the way, without knowing the details: This sounds much more like a typical database task.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Post by Villeroy »

OK one other approach:
$Sheet1.$A$1:$X$1: English German French Italian ...
Select Sheet2.A1 and enter array formula =INDEX($Sheet1.$A$1:$X$9999;0;MATCH(select_cell;$Sheet1.$A$1:$X$1;0)) [Ctrl+Shift+Enter]
9999 should include a reasonable count of rows, select_cell is a cell on another sheet with validation set to cell range $Sheet1.$A$1:$X$1 (Data>Validation... Allow: "Cell Range", Source: $Sheet1.$A$1:$X$1, [X]Show selection list).
So you pick a language from the validated cell ... forget it, I'll attach a file.
Attachments
pick_column.ods
(15.33 KiB) Downloaded 541 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
nicegreetings
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: how to define print areas by macro?

Post by nicegreetings »

Thanks for this example. Nice and new functions for me.
Although I think you are right with solving it with a database, because I already have more than 5 sheets, all multilingual.
The userer should have the possibility to chose a language and print a document with the data in this language from every sheet.
Not to go beyond the scope, I thougth of the solution with defining print areas by macro. But that seems not to work reliably.

Thanks for helping

Marco
OOo 2.4.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Post by Villeroy »

Apart from this forum beeing the wrong place, a macro solution would involve interface XPrintAreas as well as some understanding of the spreadsheet-API in general (a CellRangeAddress is not like "Sheet.A1:B").
Without knowing the details of your task, the scenario-solution "is simply the best", I belive. Knowing that scenario data are saved on a hidden sheet, named according to the scenario's name, you can even access scenario data from outside scenarios: =$English.$A$1:$A$9999 or A1 having the scenario name: =INDIRECT(A1&".$A$1:$A$9999").

If you are familiar with relational databases you should be able to create one and paste in the existing sheet data. Another advantage of a database could be, that you are free to use a database server from the beginning. Later you can move the whole thing to a network. Spreadsheets are somewhat limited. Since you mention "users", you should avoid independent file copies of your spreadsheets beeing sent across networks. This becomes a flea circus sooner or later.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply