Please, try to formulate your tasks in as much detail as possible.
I can only (with difficulty) guess that you are printing a sheet from the Print Preview mode (there is no mention of this in the topic). Indeed, in this mode the Document Controller does not have getActiveSheet method. In this case, you can remember the active sheet with each change (as you did), or go a more complicated way (for example, through the use of AccessibleContext).
I would never have thought of that.
Indeed…
I do the program for my work coleagues to use it. I have to predict every possible actions that my coleagues can do on program…If a work coleague try to use the Print Preview, He will get an error. None of my colleagues knows programming. They barely know how to open the Libreoffice menu.
I tried to use the formula =SHEET() to get the index sheet by writing
oFunction = createUnoService(“com.sun.star.sheet.FunctionAccess”)
sMyString = oFunction.callFunction(“sheet”,array())
but I get the same number (2) every time…It’s weird.
Let’s try another way. Should work in any mode (normal or Preview) .
Option Explicit
' Returns the active sheet of Calc document.
' oDoc - Calc document.
Function GetActiveSheet(ByVal oDoc) As Object
Dim oController, v
GetActiveSheet=Nothing
oController=oDoc.CurrentController
If HasUnoInterfaces(oController, "com.sun.star.sheet.XSpreadsheetView") Then
GetActiveSheet=oController.ActiveSheet
Else ' PreView Mode
For Each v In oDoc.ViewData.getByIndex(0)
If v.Name="ActiveTable" Then
GetActiveSheet=oDoc.Sheets.getByName(v.Value)
Exit Function
End If
Next v
End If
End Function
Sub Test
Msgbox GetActiveSheet(ThisComponent).Name
End Sub
1 Like
It worked great…You must be a macro code master…I can get no information about many codes in the macro lines…
“HasUnoInterfaces”
“ThisComponent.ViewData.getByIndex(0)”
v.Name=“ActiveTable”
You have a very complex knowledge.
Awesome!!!
Thanks very very very much for your attention and concern.
HasUnoInterfaces Function.
The description for Viewdata is really not very eloquent. My knowledge is the result of research through the wonderful MRI program.
He looks it up like anybody else does.
I’m no expert in Macro programming, in fact it’s a long while since I’ve used it at all and even then it was as an interface between CALC and a proper programming language (Java). If you are an experienced programmer, it’s important to realise that Macro doesn’t work like one.
In particulat, references which would normally be resolved at run-time or in an iterative process when a program is loaded, seem to be resolved in a single-pass (?) at load-time with Macro. So a “Function” statement like:
getSheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
will result in a warning because there’s NO active sheet at load-time. I’m trying to find a way around this issue at the moment.
No, you might remember something like “macros” fro old C preprocessor, but methods of classes are functions call at runtime.
.
With your idea above code would never worked, but it way reporterd to run with success.
Select a sheet yourself?
Is it necessary to run at startup?
The document’s settings.xml stores view data like this:
<config:config-item config:name="ActiveTable" config:type="string">Sheet3</config:config-item>
The original question was about printing. Yours is about loading.
This requies a new question.
BTW:
You mentioned the reason for the problem yourself: There’s no CurrentController at call time. Since you obviously assume that "current sheet" is the same trhing as ActiveSheet, there can’t be a solution.
To catch this kind of error you can use a global Boolean variable being initially False and set True by an event handler for ViewCreated.
The mentioned event handler may also directly call your macro, and the global variable can be used to exit macros for which the View must exist if it doesn’t. Depending on the use-case also an On Error Goto statement may do.
1 Like
Do you think that settings.xml is actually accessible at load-time by a macro?
If so, please tell us how.
Anyway the macro can probably not make use of settings provided for the initialisation of a CurrentController. Will the sheet objects already "exist" (e.g.)?
You can parse settings.xml before loading.
EDIT: my Python skills are limited, but the following script copied from a debug window prints out the name of the active sheet without touching LibreOffice by any means:
import zipfile
import xml.etree.ElementTree as ET
z = zipfile.ZipFile('/tmp/dummy.ods','r')
tree = ET.parse('/tmp/settings.xml')
i = tree.iter()
for e in i:
if 'ActiveTable' in e.attrib.values():
print(e.text)What specific actions are supposed to be performed before the file is finished opening?
Thanks, Wanderer, for your response.
The macro in question finds a sheet name, and it’s implemented as a function rather than a subroutine so it can be called from a spreadsheet. Also, when I wrote about “loading” the macro I meant opening the spreadsheet, not booting the system; sorry for the confusion.
The original version (from StackExchange, I think) was defined as:
Function getSheetName (Optional nSheet)
where a missing argument found the name of the active sheet, otherwise nSheet found the name of that sheet (using the CALC convention of counting from 1, not the API index from zero).
That worked well enough in practice, but a host of 10 or so message windows warning “BASIC runtime error. Object variable not set.” appeared after the initial warning that the spreadsheet contained a macro, each of which had to be to be separately dismissed.
The obvious solution was to make the argument non-optional, thus:
Function getSheetNameTest(sheetNbr As Long) As String
If sheetNbr >= 0 Then
If sheetNbr = 0 Then
getSheetNameTest = ThisComponent.getCurrentController().getActiveSheet().getName()
Else
getSheetNameTest = ThisComponent.getSheets().getByIndex(sheetNbr-1).getName()
EndIf
Else
MsgBox ("getSheetNameTest(arg) : 'arg' must be >=0")
EndIf
End Function
That also works fine but for one thing. Any non-zero value for sheetNbr works fine; saving a test spreadsheet with (say) a value of 2 will display the sheet name correctly, the spreadsheet can be saved & reopened with no warnings, and the cell containing the formula - =getSheetNameTest(2) - displays the correct sheet name on reopening.
But a value of ‘0’ brings up the BASIC runtime warning mentioned earlier, the cell containing the formula is blank, and the zero value must be re-entered fro the keyboard. Surely ‘0’ is a valid value for a long type?
It is, but thats nit the crucial point. (But you may try to do a division /0 and will not succeed…)
.
Your code shows different behaviour for 0:
But the error is only occurring, because you did something else. You used the code as UDF/user defined function:
And the recalculation of formulas at startup does not work, because there is no ActiveSheet at this time.
Another way of coding may be, NOT to use a function, but to write the name in a cell by subroutine. This routine may be called after loading the sheets…
As Lupp and Wanderer have confirmed, the fundamental problem is insoluble in any elegant way within the constraints of Macro, and I think Lupp’s suggestion of a global Boolean variable set by an event handler for ViewCreated is about as elegant as it gets.
However the SHEET() implementation seems to have dealt with this issue. So the function will now only find a sheet-name where the CALC sheet number is available, and for the sake of getting the original use-case going quickly in an compatible way, the case where the calling sheet number is unavailable will be handled thus:
=getSheetNameTest (SHEET())
As background, I have globally scoped formulae for finding the (highly variable) range of a column in any given sheet of a multi-sheet workbook, for example:
colA = INDIRECT("$A$2:$A$" & TEXT(ROW(INDIRECT(GETSHEETNAME(SHEET()) & “.End”))-1,“0”))
and similarly for colB, colC…
These can then be used to search any given column for a given string using Regular Expressions, where the same expression applies in every sheet once it’s defined globally. This basic form can be extended further in obvious ways for use in SUMIFS() etc.
WARNING: This solution has had only superficial testing!
Note that getCurrentController().getActiveSheet() has nothing to do with “the sheet where this formula is”. It is “the sheet that is currently selected / shown to the user”. This means, by the way, that a UDF using your code would return different results, when recalculated, depending which sheet is active - even when itself is on a different sheet. This may create very unexpected things, when cells are recalculated behind the scene.
So basically, the error shows your mistake: you imagine you are dealing with the current formula’s sheet, but actually use a UI-related thing, that is naturally unavailable until the UI is set up.
And no, there is no information in UDF about its position.
2 Likes