Python Macro

Writing a macro in Python for renaming cells turned out to be simple. Use of the dir command and just trying various options helped.

def chgSheet1RangeNames( ):
    """This macro changes names to the Excel values"""
#get the doc from the scripting context which is made available to all scripts
    model = XSCRIPTCONTEXT.getDocument()
    names = model.NamedRanges
    name_list = [names.getByIndex(i).getName() for i in range(names.getCount()) 
        if names.getByIndex(i).getName().startswith('sheet1')
        and names.getByIndex(i).getName().endswith('_2')]
    for n in name_list:
        rng = names.getByName(n)
        rng.setName(n[:-2])
    return None

I selected the names which needed to be renamed and renamed them one at a time.

Unfortunately, some hidden functions gave errors. So, the best option was to create additional names for the same ranges. This was also easier than expected. Instead of

rng.setName(n[:-2])

I used

names.addNewByName(n[:-2], rng.getContent(), rng.getReferencePosition(), 0)

Why numbering should start at zero

Possibly because I learnt programming in Fortran and Pascal, starting an index with 0 and the syntax of Python range statement had seemed unnatural to me. So, it was nice to come across Dijkstra's Why numbering should start at zero.

Now, I am a convert and nice to unlearn Fortran.

"Adhering to convention a) yields, when starting with subscript 1, the subscript range 1 ≤ i < N+1; starting with 0, however, gives the nicer range 0 ≤ i < N. So let us let our ordinals start at zero: an element's ordinal (subscript) equals the number of elements preceding it in the sequence. And the moral of the story is that we had better regard —after all those centuries!— zero as a most natural number."

Python Programming for Friends

I was pleasantly surprised by two requests for copies of the articles I had written in Linux for You, aimed at people who may not be computer engineers.

So, finally, yesterday on Gandhi Jayanti, with a lot of help from my wife, we added the articles on http://sethanil.com

Hopefully, they will be of use to some people over the years.