Mass font change for comments

[datadriven]

Is it possible in Calc to select all of the notes you've created and do a mass font change? I need to change font type & size on 1,500+ comments across 10 sheets and really would like to avoid doing it one-by-one. Also, I have 3.3, not what is mentioned in my signature.

[RoryOF]

The only way I can see to do this is to open each of the archives that is the .ods file, and globally find and replace the font formatting for the comments, then reseal each of the archives.

[datadriven]

Awesome! However, I'm super computer savvy. Can you provide some direction? The file has always been a .xls if that makes a difference...?

[RoryOF]

That does make a difference. When working in its native formats OpenOffice keeps each such filet (.ods in this case) as an archive which can be opened with an archive manager and the individual components edited (when one knows what one is doing). But how .xls files are stored is another matter, and I don't know how to approach this. One quick and dirty method might be to alias the font in use to the desired font, so that it appears as you require, even if called by an incorrect name.

[datadriven]

How can I alias the font in use? I'm up for it, and can usually figure things out quickly, but it's something I'm currently unaware of...

[RoryOF]

I found this relative to Ubuntu.
http://manpages.ubuntu.com/manpages/har ... ias.8.html
How one would do it on a Mac I cannot say.

[datadriven]

Much thanks! I'll give it a shot and post about whether it works...

[Charlie Young]

Here is a Python macro to change all comments in all open spreadsheet documents. It is quite indifferent to whether they are .xls, .ods, and also as to operating system.

Assign desired values to CommentFont and CommentHeight. It should be apparent that this could also be adapted to other properties.

Code: Select all

import uno
import unohelper

context = XSCRIPTCONTEXT
def FixComments(*dummy):
    CommentFont = "Times New Roman"
    CommentHeight = 12.0
    ctx = context.getComponentContext()
    desktop = ctx.getServiceManager().createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
    xEnumeration = desktop.getComponents().createEnumeration()
    while xEnumeration.hasMoreElements():
        oDoc = xEnumeration.nextElement()
        try:
            if oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument"):
                oSheets = oDoc.getSheets()
                for i in range(oSheets.getCount()):
                    oSheet = oSheets.getByIndex(i)
                    Comments = oSheet.getAnnotations()
                    for j in range(Comments.getCount()):
                        oShape = Comments.getByIndex(j).getAnnotationShape()
                        oShape.setPropertyValue("CharFontName",CommentFont)
                        oShape.setPropertyValue("CharHeight",CommentHeight)
                        oCursor = oShape.createTextCursor()
                        oCursor.gotoStart(False)
                        oCursor.gotoEnd(True)
                        oCursor.setPropertyValue("CharFontName",CommentFont)
                        oCursor.setPropertyValue("CharHeight",CommentHeight)
        except:
            pass
                    
g_exportedScripts = FixComments,

[datadriven]

Thank you so much Charlie! This should be great. The only problem is I so computer illiterate that I don't even know how to apply or run this macro. Any suggestions? Thanks!

[datadriven]

I added my font & height but keep getting the following error message:

BASIC syntax error.
Expected: Sub.

[datadriven]

Somebody please help me. How on earth do I actually apply or use this phython macro? This is my first exposure to macros...I cannot exaggerate how little I know about this stuff.

[datadriven]

I tried going to: Tools > Macros > Organize Macros > Python. It does not give me an option to run, create or edit.

[Charlie Young]

Somebody please help me. How on earth do I actually apply or use this phython macro? This is my first exposure to macros...I cannot exaggerate how little I know about this stuff.

You need to find your user profile. I don't know where it is on a Mac, so perhaps someone else can explain that if needed.

Once you've found it, there will be a Scripts folder inside. In the Scripts folder, you need to create a python folder, which must be lower case. Save the above Python code in a text file in the python folder, using any old text editor, but give it the extension .py. Say, FixComments.py.

After that, do Tools > Macros > Run, and you should find FixComments under My Macros in the Library box, with the actual FixComments macro name in the macro box to the right. Just select it and hit run. You can also do Tools > Macros > Organize macros > Python, and run it through there.

Remember you want to have your comment filled document open. I'd try it on a backup first though.

[datadriven]

Thanks so much. I'll give this a shot. I also have a PC so I maybe I should just try it there...

[datadriven]

Man, I can't catch a break. I'm trying to save the .py file in the scripts folder (on PC) but it's telling me I don't have permission to save to that loction...

[datadriven]

maybe it's not saving as a proper .py file? I put .py at the end but I think it's still saving as a text file.

[datadriven]

OK, I got the IDLE program and created an actual .py file. It even shows up now when I go to Tools > Macros > Organize macros > Python. But the "run" button is inactive. I'm so close. Is there anyway to make it active?

[datadriven]

Why would the sample .py files "hello world" and "capitalize" be active and able to "run," but not the fixcomment.py file I created?

[Charlie Young]

OK, I got the IDLE program and created an actual .py file. It even shows up now when I go to Tools > Macros > Organize macros > Python. But the "run" button is inactive. I'm so close. Is there anyway to make it active?

Can you see the FixComments macro in the macro box? If not, there is probably something wrong with the formatting. One thing to watch out for is that tabs shouldn't actually be tabs, but (typically) 4 spaces. I use an editor that recognizes this for Python, but it would be strange if IDLE didn't handle that. I use IDLE for testing stuff, but not to create the files.

[datadriven]

I do see FixComments in the macro box. But it won't allow me to click "run." Just just seems to appear as a folder with nothing in it when I go to Tools > Macros > Organize macros > Python....unlike helloworld and capitalize, which appear as a folder with a file in it....if that makes sense. But in my user profile everything looked the same when I added FixComments.py.

[Charlie Young]

The attached is a copy of FixComments.py in a .zip file. Try extracting the .py from it and copy that to your user/Scripts/python folder.

[datadriven]

here's how the .py was set up. I just cut and pasted the script. also a screen capture of what i'm getting when i try to run the macro.

[datadriven]

Thanks so much! It's showing up now! I'll run it...

[datadriven]

does it take a while to run? it didn't seem to change the fonts

[datadriven]

again, i really appreciate all the time you've put in to help me...

[Charlie Young]

does it take a while to run? it didn't seem to change the fonts

With 1500 comments it might take a while, but did you set CommentFont and CommentHeight to what you want?

[datadriven]

Yes, I changed the font type & size before I ran it. I'll just let it run it's course for a while and see what happens...