Ask Your Question

Can Freeze Panes be set by macro while file is open in Hidden mode

asked 2018-07-04 18:55:49 +0200

Tavy_Dog_Walker gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-07-05 14:54:32 +0200

JohnSUN gravatar image

updated 2018-07-05 14:55:14 +0200

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
    OpenProp(0).name="Hidden":  OpenProp(0).value= True 
    oDoc = OpenDocument("private:factory/scalc", OpenProp)
Rem ... collect data from 50+ files 
    oCController = oDoc.getCurrentController()
        oCController.freezeAtPosition(1, 2) ' Freeze on B3
    Loop Until oCController.hasFrozenPanes()
Rem ... Something else
End Sub
edit flag offensive delete link more


I 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

Tavy_Dog_Walker gravatar imageTavy_Dog_Walker ( 2018-07-05 18:01:43 +0200 )edit

What about to try to pre-install the frozen panels in the template file and use it as a basis for the future workbook?

JohnSUN gravatar imageJohnSUN ( 2018-07-05 18:20:46 +0200 )edit

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.

Tavy_Dog_Walker gravatar imageTavy_Dog_Walker ( 2018-07-05 18:31:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-07-04 18:55:49 +0200

Seen: 196 times

Last updated: Jul 05 '18