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

Creating a macro - Writing a Script - Using the API

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

Postby nicegreetings » Mon Feb 04, 2008 2:31 pm

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
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: how to define print areas by macro?

Postby Villeroy » Mon Feb 04, 2008 2:52 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Postby nicegreetings » Mon Feb 04, 2008 3:24 pm

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
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: how to define print areas by macro?

Postby Villeroy » Mon Feb 04, 2008 4:15 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Postby Villeroy » Mon Feb 04, 2008 5:24 pm

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 347 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how to define print areas by macro?

Postby nicegreetings » Tue Feb 05, 2008 10:04 am

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
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: how to define print areas by macro?

Postby Villeroy » Tue Feb 05, 2008 2:05 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26730
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests