Sorting Excel Range with Datanitro

Question

I am trying to sort a range of cells in excel using Datanitro. It doesn't look like Datanitro has a built-in sort method, so my options are to either create one in python, which is what datanitro appears to have done here, or use the built-in VBA method and call that method from python via datanitro.

A simple VBA subroutine for sorting is the following:

Sub Sort_1(strDataRange As Range, keyRange As Range)

strDataRange.Sort Key1:=keyRange, Header:=xlNo, Order1:=xlDescending

End Sub

Now I just need to call this from python and pass the subroutine two Range objects. However, the following code in my python shell produces a NameError: 'Range' is not defined

VBA('Sort_1', [Range('A5:IF20'), Range('M5')])

In addition, if pass both arguments as strings I get a NitroException: failed to run excel macro, presumably because the subroutine is expecting Range objects rather than strings.

An easy workaround is define the range within the subroutine, but then it can't be dynamic with variables in my python code.

Any suggestions? I would prefer to go the VBA route because the method is already nicely defined in VBA and I don't have to waste time creating any additional python methods.

Answer 1

DataNitro's philosophy is to use regular Python features whenever possible, primarily because Python has a ton of functionality.

For example, here's one way to sort a table:

from operator import itemgetter

def table_sort(table_range, column):
    data = table_range.table
    sorted_data = sorted(data, key = itemgetter(column))
    table_range.data = sorted_data

For example, table_sort(CellRange("A1:E10"), 0) would sort A1:E10 by column A.

In practice, you would do this in one line:

def table_sort(table_range, column):
    table_range.table = sorted(table_range.table, key = itemgetter(column))