Monday, August 23, 2010

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)
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


I used

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

1 comment:

  1. Great making Calc use the Indian income tax form!
    Great work well done.