I have a process that summarizes data from 50+ Calc files that are all individually updated each month, by different users, into a single file. This file is created in hidden mode so as not to distract the user until all processing is completed and the file is made available for administrators to view. To improve interpretation/ analysis of the data I would like to freeze cells on a sheet in the output file before is made available so that users have the benefit of column and row headings if they opt to scan the data rather than using Pivot charts (the subject of a previous answered question).
I have tried using dispatcher calls andr Currentcontroller.FreezeAtPosition coding but both only seem apply when the target file is visible.
Does anyone have any suggestions how I can achieve this while the file is still hidden and being constructed by code?
AFAIK it’s impossible - you must set to ContainerWindow visible=True (and wait small time!) before use oDoc.CurrentController.freezeAtPosition(nColumns, nRows) To perform this operation, the Controller needs information about Spreadsheet View, which is not available until the spreadsheet is hidden.
So, it can be something like this
Sub DemoFreezeHiddenSheet
Dim oDoc As Variant, oCController As Variant
Dim OpenProp(0) as New com.sun.star.beans.PropertyValue
OpenProp(0).name="Hidden": OpenProp(0).value= True
GlobalScope.BasicLibraries.LoadLibrary("Tools")
oDoc = OpenDocument("private:factory/scalc", OpenProp)
Rem ... collect data from 50+ files
oCController = oDoc.getCurrentController()
oCController.getFrame().getContainerWindow().setVisible(True)
Do
oCController.freezeAtPosition(1, 2) ' Freeze on B3
Loop Until oCController.hasFrozenPanes()
oCController.getFrame().getContainerWindow().setVisible(False)
Rem ... Something else
oDoc.storeToURL(ConvertToURL("D:\myFolder\myTest.ods"),Array())
oDoc.close(True)
End SubI had a feeling that this may be the case. I have tried a solution similar to your suggestion that works. It just has the hidden file flash into view for a moment before the frozen cells are set and the file is saved and closed. I had hoped it would be an attribute of each sheet and I’d missed it. I’ll submit it as a suggestion for future development consideration as it may help others like myself that are proficient in MS VBA who are now also using LO or similar.
Thanks for responding
What about to try to pre-install the frozen panels in the template file and use it as a basis for the future workbook?
Yes that would certainly work for one of the sheets, but needs to be a calculated position for two others due to the variable nature of the data which prevents setting in the template. I’ll look at pre-processing the data to see if I can predict the data shape before extracting and processing it.
A good helpful thought.