Porting Applescript to Scripting Bridge: can't get value of range in Excel 2008

Question

I have a need to move some Applescript code to Scripting Bridge to take advantage of some Cocoa hooks without the need for Applescript-ObjC.

Using Excel 2008 with Applescript, getting the value of a range is easy:

set myList to GetValuesFromColumnRange("A", 1, 100)

 on GetValuesFromColumnRange(ColumnLetter, FirstRow, LastRow) -- (string, integer, integer) as list
     set theList to {}
     tell application "Microsoft Excel"
         set theRange to ColumnLetter & FirstRow & ":" & ColumnLetter & LastRow
         set AppleScript's text item delimiters to {return}
         set theList to (get value of range theRange as list)
         set AppleScript's text item delimiters to {""}
     end tell
     set theList to ConvertItemizedListToValueList(theList) of me -- Note this dependency due to how MSE2008 returns the data
     return theList
 end GetValuesFromColumnRange

But in Scripting Bridge, I'm having a problem getting the cells of a worksheet based on a range. The following is what I have so far.

    Excel2008Application *excelApp = [SBApplication applicationWithBundleIdentifier:@"com.microsoft.Excel"];

    Excel2008Workbook *workbook = excelApp.activeWorkbook;

    SBElementArray *sheets = [workbook sheets];

    Excel2008Sheet *targetSheet;
    int thisSheet = 0;
    int lastSheet = [sheets count]; 

    for (thisSheet = 0; thisSheet < lastSheet; thisSheet++) {
        Excel2008Sheet *currentSheet = [sheets objectAtIndex:thisSheet];
        NSString *currentSheetName = currentSheet.name;
        if ([currentSheetName isEqualToString:@"Metadata"]) {
            targetSheet = currentSheet;
            [targetSheet retain];
        }
    }

    Excel2008Range *range = [[[excelApp classForScriptingClass:@"range"] alloc] initWithProperties:[NSDictionary dictionaryWithObjectsAndKeys:@"A1:A10", @"formulaR1c1", nil]];

    [[targetSheet ranges] addObject:range];
    range = [[targetSheet ranges] lastObject]; // not null; stated class in log: MicrosoftExcelRange

    Excel2008Sheet *valueSheet = range.worksheetObject; // supposed to be new worksheet based upon values within the range; not null; stated class in log: MicrosoftExcelSheet
    [valueSheet retain];

    SBElementArray *valueCells = [valueSheet cells]; // not null, but count is 0
    [valueCells retain];

The problem comes with the last line, when I actually get the cells from valueSheet, in that the returned SBElementArray isn't null, but it also doesn't contain any objects. The same goes for getting the cells in targetSheet as well.

Documentation to do this, near as I can tell from all my searching, does not exist and I've taken this about as far as I can.

score 1 · Accepted Answer · answered Dec 14 '10 at 17:19

Solved.

NSString *rangeName = @"A1:A10";

Excel2008Range *range = [[[excelApp classForScriptingClass:@"range"] alloc] initWithProperties:[NSDictionary dictionaryWithObjectsAndKeys:rangeName, @"name", nil]];
[[targetSheet ranges] addObject:range];

Excel2008Range *currentRange;
if ([[[targetSheet ranges] objectWithName:rangeName] exists]) {
    currentRange = [[targetSheet ranges] objectWithName:rangeName];
}

NSLog(@"[currentRange.value get] = %@", [currentRange.value get]);
// result: ((key),(1),(2),(3),(4),(5),(6),(7),(8),(9)) = NSArray

It seems the trick is to set the range string into the name property of the Excel2008Range. The one pitfall I had found while working on this is to never populate the formulaR1c1 of the range—as in [NSDictionary dictionaryWithObjectsAndKeys:rangeName, @"formulaR1c1", nil]—because that will populate the range with the value of the range string.

In hindsight this actually makes sense when reading the syntax of the exact same command in both Applescript...

tell application "Microsoft Excel"
    set theValues to get value of range "A1:A10"
end tell

...and objc-appscript...

NSString *rangeString = @"A1:A10"
MEApplication *microsoftExcel = [MEApplication applicationWithName: @"Microsoft Excel"];
MEReference *cells = [[[microsoftExcel ranges] byName:rangeString] value];
NSArray *cellValues = [cells getItem];

In both cases the value of the range is gained by getting a range of values with its name. But with Scripting Bridge, near as I can tell, the name has to be applied explicitly upon creation of the range object.

I'm sure there is a better way of accomplishing this (there usually is), but at least this works.

Porting Applescript to Scripting Bridge: can't get value of range in Excel 2008

1 Answers1