Comments in Calc always showing in xlsx file

try saving it as .ods and see if that helps

Thanks. I did try that and it does work. However, this is a shared document and I need to save it as an xlsx document to share it with my co-workers. I tried to open it in OpenOffice 3.3, which is the latest. That works ok but unfortunately it won't allow me to save it as an xlsx file. It's not one of the available format options.

Hi

I'm sure, your co-workers can also open .xls files!

Karo

xlsx is not a format designed for sharing, it was designed to be microsoft compatible. OOXML, the standard MS pushed through (when ODF already existed), is also not designed to make sharing easy but to make conversion of old, proprietary, MS file formats reliable. To make matters worse, MS office 2007 and 2010 do not comply with the OOXML standard.

Which all means?

You are out of luck if you are stuck with using Microsoft format documents. MS office 2010 can work with ods (as can 2007 with service pack 2) so you might be in luck if you try using ods (but MS probably has issues with the conversion of ods -> xlsx in the same way as LO does the other way round)

Ok, thanks for the information. Unfortunately it seems right now using Microsoft format documents is the standard.

It wouldn't be so bad if I can easily toggle "Show Comments" easily. Is there a way to toggle the Comments for all the cells at once instead of one at a time?

There are many cells that has Comments so it makes it hard to see the cells without turning it off. Reading the documents, as far as I can tell I have to do this one cell at a time by right-clicking on the cell.

Hi

Here are a Basic macro to hide all Annotations in a snatch:

---

sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            annocells.nextElement.getAnnotation.setisVisible( false )
        loop
    next i
    end with
end sub

---

Karo

Thanks, however I'm not familiar with using macros.

I selected Tools - Macros - Organize Macros - LibreOffice Basic. I selected my document and the "New" button to put your code

I get the following error

*********************

BASIC runtime error.

Property or method not found:getAnnotation.

Hi

The Code above works fine here, what tell your Interpreter if you change to:

---

sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            cell = annocells.nextElement

            cell.Annotation.setisVisible( false )
        loop
    next i
    end with
end sub

---

Karo

Hi

Ok, my fault i don't test against consecutive cells with Comments.

Here it is:

---

sub hideAnnotations
    with thisComponent.sheets
    for i = 0 to .count-1
        annocells =    .getbyindex(i).querycontentcells(8).createEnumeration
        do while annocells.hasmoreElements()
            part() = annocells.nextElement
            if not part.supportsService("com.sun.star.table.Cell") then
                for r = 0 to part.getRows.Count -1
                    for c = 0 to part.getColumns.Count -1
                        part.getCellByPosition(c, r).Annotation.setisVisible( false )
                    next c
                next r
            else
                part.Annotation.setisVisible( false )
            end if
        loop
    next i
    end with
end sub

---

Karo

Thank you, this works.

There is a related problem. After creating the macro and testing that it works I saved it. After I close the document and reopen it the macro is not there anymore. Apparently it's not being saved.

There is also another unrelated issue that I just discovered that will force me to use MS Office. If I edit something in that document and someone uses MS Office to open it, they get a message that some cells are corrupted and needs to be rebuilt. I don't see this error when opening the same document in LibreOffice

I just ran into this same problem today.  The solution I have found is to open the file in Excel and save it as an Excel '97/2003 .xls file.  Do this and then share the XLS file with everyone.  Both LibreOffice and MS Office will then open the file with comments working properly.  Just tell all the MS Office users NOT to update the format to .xlsx.  If you have access to the people's computers that use MS Office, you could go in and change their default file save format to .xls.

I agree that this could be a solution but unfortunately it's not a realistic solution in our company.

First of all, I would not want other people accessing my computer and change settings even if they knew my user id and password. MS Office is what the majority of the people in our company uses and we distribute templates to use. These templates are in .xlsx format. There are also macros and Excel Add-ins that are used.

Hi, I was a happy user using the macro above, but after upgrading to LO 3.6.0 it stopped working. Maybe someone can fix it for me.

Thanks a lot!

Hallo

sub hideAnnotations
    with thisComponent.sheets()
    for i = 0 to .count -1
        annos = .getbyIndex(i).Annotations
        for n = 0 to annos.count -1
            annos.getbyIndex(n).setisVisible(0)
        next n
    next i
    end with   
end sub

Karolus

Thanks a lot!, will test it ASAP

An additional info… It was useful for me.
You can try this extension to show or hide all comments:
http://extensions.libreoffice.org/extension-center/shownotes

thanks! i will try it when i have time.

This thread is still the top hit on Google for this issue so folks will be going away thinking this is the status quo. Feel free to attack this post to an old thread if you think that is more important.

This behaviour was a bug in LibreOffice. It was introduced quite recently (3.5.x I think), was fairly quickly identified and is fixed in the 4.0 release (certainly on Ubuntu, probably on Windows etc as well). Anyone having this problem with xlsx files might want to consider upgrading to 4.0. See bug tracking here: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/990868
I run LibreOffice on an Ubuntu server which isn’t always up to date and thus caught me out, I suspect I’m not the only one. Nice work on the macros btw, but novices will find it easier to upgrade to 4.0 now.

Some of the posts on here sound like LibreOffice is about trying to build the Betamax of office suites. It’s not. No-one wants to be a footnote in history saying “LibreOffice was better in theory but now we all use MS Office”. Working with xlsx is important, the developers are smart and they know this and I think they work pretty hard to make sure it keeps working.

As per the prior comment, the original (now resolved) bug for this issue is FDO#46278. The problem has been fixed for the v3.6 and v4.0 series, but not v3.5.7.2 (comments state v3.5.8, but this was never released).