Losing Freeze Rows and Columns

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Losing Freeze Rows and Columns

Post by AccessShell »

when I create a CALC spreadsheet, sometimes I freeze an area on the spreadsheet. This works fine before and after saving the spreadsheet.
However, I add a line (row) of data every day from a VB6 program. (I finally learned how to do this). However, after the spreadsheet is saved and closed I no longer have the rows and columns frozen. Noticed when I reopen the spreadsheet.

Is this feature or a bug when using VB6? If I am doing this from my code I am not aware of it. Currently I don't know how to do this. I hope I do not have to add to code after I add the new row to refreeze the sheet every time I add data.

When I add the new row, I also update some charts on another tab.

What am I missing.

Thanks
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Losing Freeze Rows and Columns

Post by RoryOF »

In which format do you Save your spreadsheets? .ods is best.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

I save it in .ods
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

Have you tried refreezing it - very simple -

viewtopic.php?f=45&t=65964
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

Of course. I just don't want it happen when I run the VB6 code.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

What difference does it make? You add a line of code - its fixed before, as a user, you could possibly notice.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

The program adds a line of data. It should not change anything else. The freeze is at line 5. The new line of data as currently ar about line 1060. The purpose of the freeze is to see the column titles. Having to manually go in to the spreadsheet every time the program is executed is ridiculous to reapply the freeze.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

I'm confused now...

Initially I read you as... you run a program which somehow removes the freeze and I don't see why a freeze couldn't be added back as the last line in the code?

But now you're talking about "having to manually go in to the spreadsheet"?

There are events such as the document load event where you can run a macro to add a freeze without having to manually do it.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

1. The current program has no code to remove a freeze.
2. I should not have to write code to reestablish the freeze.
3. I want to know why this is happening.
4. I don't think it is too much to ask why this is happening.
5. You are assuming that the unfreeze happens when I open the .ods file from VB6. I don't know that. It may be on closing. It may be on adding the line of data.
6. If LO Calc cannot keep a freeze, that may explain why it is not as popular for VB6 to interact wiht OL!
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

It may be on closing. It may be on adding the line of data.
This is something you can investigate yourself. Try doing it with just closing. Then just add the line of data and see if that does it. Go through your program till you pin down where it happens.

The number of people doing Basic on OO/LO, going by the numbers on this forum, isn't large. The numbers automating it with VB6 is probably almost nobody.

Its the nature of OO and LO that they have a lot of quirks and many bugs too.

We don't have your code... and most people here won't have VB6 anyway... so you're possibly asking the impossible... of being able to tell you why you're getting what you're getting without those. But you can pin down where its happening yourself.
Last edited by JeJe on Fri Apr 29, 2022 11:03 am, edited 1 time in total.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

I found out where I am losing the freeze. I don't understand why. All I did was open the spreadsheet and get access to the specific sheet.
As follows:

Code: Select all

    Set oSM = CreateObject("com.sun.star.ServiceManager")

    Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")

    'Open existing doc
    strFNPrefix = "file:///"
    strFileName = "C:/Net Worth/Daily Activity/UPD/Writer to Calc/Test.ods"
    Set OpenParam(0) = mAkePropertyValue("Hidden", True)  ' Open the file hidden
    Set oDoc = oDesk.loadComponentFromURL(strFNPrefix & strFileName, "_blank", 0, OpenParam)
    
    'Get the Worksheet
    Set oSheets = oDoc.getSheets()
    Set oSheet = oSheets.getByName("Activity")
In my walkthru, instead of adding the new data, I saved the file and closed the spreadsheet.

Code: Select all

'***  Close the  Workbook
    oDoc.store
    oDoc.Close (True)
    Set oDoc = Nothing
    'Set oDesk = Nothing
    oDesk.Terminate
    Set oSM = Nothing

As to another point in the previous post. It may be true that the number of people using VB6 with LO CALC is very, very small, there are people using other languages. I noticed Python. I do not remember the others. I cannot believe that VB6 is causing the issue. This must be happening in other languages. Maybe, no one else has tried a freeze in any other language.

SO why don't I program a freeze when I finish adding the new data. Two reasons.
1. I don't know how to do it.
2. Even if I did, I don't know if it would hold.
3. Yes, there's one more. If I had a client, how would I tell them that LO does not allow it?
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

Please strike the last sentence from the previous post. It was completely uncalled for.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

Do you mean my last sentence? Maybe something's changing in the translation or something?

This link I posted showed you how to add the freeze in code.

Code: Select all

doc.currentController.freezeAtPosition(2,2)
Maybe opening the file hidden is what removes the freeze - you could try not hiding it and see if that's different.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

I meant the last sentence in my post dated Thu Apr 28, 2022 10:05 pm
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

JeJe your code does not work. On the first pass, I got an error msg (it wants an equal sign). So I changed the statement to

Code: Select all

RV = oDoc.currentController.freezeAtPosition(5, 5)
where RV is

Code: Select all

Dim RV  As Boolean
(Return Value). This executes, but does nothing to reestablish the freeze.
In my code, I don't think "CurrentController" is valid. I even tried to replace oDoc with oSheet. That didn't work either.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

Again... it might be because the document is hidden.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

I tried it with the document = Visible. The freeze is NOT lost.
I don't understand why.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

try this as a different method of getting the controller:

Code: Select all

doc.Controllers.nextelement.freezeAtPosition(2,2)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

Sorry. It still loses the freeze on hidden.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

You could try briefly showing the document, and applying the freeze before closing it

Code: Select all

ODOC.controllers.nextelement.frame.containerwindow.setvisible true
ODOC.controllers.nextelement.freezeAtPosition(2,2)
'then close it
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
AccessShell
Posts: 47
Joined: Tue Apr 05, 2022 2:17 am

Re: Losing Freeze Rows and Columns

Post by AccessShell »

I think we are miscommunicating. If I open as hidden, I lose the freeze. If I open as visible, I retain the freeze. If I open as visible, why would I have to add code to freeze?
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Losing Freeze Rows and Columns

Post by JeJe »

-open it hidden
-position it so it can't be seen. Most commonly off screen using the windows api - plenty of VB6 code out there for that.
-then make it visible (it still can't be seen as its off screen)

If it displayed so briefly that its not seen or isn't distracting enough anyway - then you don't need to do the positioning off screen step.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply