Page 1 of 1

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

PostPosted: Mon Feb 04, 2008 2:31 pm
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

Re: how to define print areas by macro?

PostPosted: Mon Feb 04, 2008 2:52 pm
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.

Re: how to define print areas by macro?

PostPosted: Mon Feb 04, 2008 3:24 pm
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

Re: how to define print areas by macro?

PostPosted: Mon Feb 04, 2008 4:15 pm
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.

Re: how to define print areas by macro?

PostPosted: Mon Feb 04, 2008 5:24 pm
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.

Re: how to define print areas by macro?

PostPosted: Tue Feb 05, 2008 10:04 am
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

Re: how to define print areas by macro?

PostPosted: Tue Feb 05, 2008 2:05 pm
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.