Losing Freeze Rows and Columns
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Losing Freeze Rows and Columns
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
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
Re: Losing Freeze Rows and Columns
In which format do you Save your spreadsheets? .ods is best.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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
Re: Losing Freeze Rows and Columns
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)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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
Re: Losing Freeze Rows and Columns
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.
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)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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!
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
Re: Losing Freeze Rows and Columns
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.It may be on closing. It may be on adding the line of data.
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.
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)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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:
In my walkthru, instead of adding the new data, I saved the file and closed the spreadsheet.
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?
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")
Code: Select all
'*** Close the Workbook
oDoc.store
oDoc.Close (True)
Set oDoc = Nothing
'Set oDesk = Nothing
oDesk.Terminate
Set oSM = Nothing
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
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
Please strike the last sentence from the previous post. It was completely uncalled for.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
Re: Losing Freeze Rows and Columns
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.
Maybe opening the file hidden is what removes the freeze - you could try not hiding it and see if that's different.
This link I posted showed you how to add the freeze in code.
Code: Select all
doc.currentController.freezeAtPosition(2,2)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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
where RV is (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.
Code: Select all
RV = oDoc.currentController.freezeAtPosition(5, 5)
Code: Select all
Dim RV As Boolean
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
Re: Losing Freeze Rows and Columns
Again... it might be because the document is hidden.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
I tried it with the document = Visible. The freeze is NOT lost.
I don't understand why.
I don't understand why.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
Re: Losing Freeze Rows and Columns
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)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
Sorry. It still loses the freeze on hidden.
LibreOffice 7.3.3.2 (x64) on Windows 10 Home
Re: Losing Freeze Rows and Columns
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)
- AccessShell
- Posts: 47
- Joined: Tue Apr 05, 2022 2:17 am
Re: Losing Freeze Rows and Columns
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
Re: Losing Freeze Rows and Columns
-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.
-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)