Building a Dynamic Range

Scripting Forums AppleScript | Mac OS X
,
#1

I am trying to build a range that summarizes data on a worksheet and I do not know at the outset how many rows I will have in my final table. I am working outside of Excel to speed up execution.

	tell worksheet "ExpiredoptDate"
		set UsedRows to ((first row index of (get end (last cell of column 1) direction toward the top)))
		set ExpiredStocks to the string value of range ("A3:BL" & UsedRows)
end tell

As the following routine shows I have set a range ending at row 10. I have not included the commands that consolidate the data.

set Stocks2 to string value of range ("A3:H10") of worksheet "sheet8"
		set RowConsolCnt to 1 --1st Row of Consolidated Data
		repeat with Stock from 1 to (count of Stocks)
			set RowConsolCnt to RowConsolCnt + 1
		end repeat
		set value of range ("A3:H" & (3 + RowConsolCnt)) of worksheet "Sheet8" to Stocks2--the 3 allows for headers

However I have no way of knowing at the start how many rows there will be. At present I set the row number hoping it will be enough but as my database grows that will change . I tried resetting the string value of “Stocks2” on each iteration of the repeat loop but that of course did not work.

Any suggestion very much appreciated .

#2

I’m having difficulty understanding your problem (and the connection between your two snippets of code). Can you clarify? It looks like you’re putting the values of a range into a variable, and then putting the variable back into the range.

If you just want to build a dynamic range (and your spreadsheet isn’t massive) then try using ‘rows of used range’, like so:

tell application "Microsoft Excel"
	with timeout of 5 seconds
		tell workbook 1
			tell worksheet 1
				
				set ra to 3
				set rb to count of rows of used range --> 20
				set rc to ra & ":" & rb as text --> "3:20"
				
				set cc to "A:J"
				
				set bRg to intersect range1 row rc range2 column cc
				string value of bRg
			end tell
		end tell
	end timeout
end tell

As you add rows, the used range will expand accordingly. I imagine that should your spreadsheet grow to millions of cells then there might be a faster way to determine the last row.

#3

Hi first thank you for taking the trouble to reply.

I started building it in Excel and as you said it expands without a problem.

I tried changing to try and work outside excel to reduce time, that may well just be waste of time on my part. Presently the table is about 400 rows and 46 columns but growing . The consolidator in my second code snippet has a second repeat block which works on a date cell in the Excel worksheet.

The first variable is the table referred to above the second “Stocks2” is the consolidation table and in my example list is set to 10 rows of data. “A3:H10” the “RowConsolCnt” counts how many results their are, and has you can see I solve my issue by setting the output range row by reference to that variable.

Its the “H10” I want to change dynamically if possible which of course it may not be. Using a very small data set with the last row set at “H100” execution was just over a second but if I change to “H10000” the time goes to over 10 seconds.

#4

I think that you might get the best performance by having Excel handle row counting automatically for you. For example, in one of your header cells (but not in column A), put this formula: COUNTA(A:A). It will count all the cells in that column with a value/formula in it. The column you choose to count should have a value or formula in every cell. You could put this counting cell anywhere that’s convenient though.

Then, in your script, get that value and work with it. Then it won’t really matter how large your spreadsheet grows.

#5

You don’t even need to put that counting formula in your worksheet; you can do it in code.


tell application "Microsoft Excel"
	evaluate name "=COUNT($A:$A)"
end tell

You can also, depending how your data is structured, set up named ranges in your worksheet and reference those directly.

#6

That’s a good suggestion re: evaluate, but the OP seems concerned about performance and I’m not sure how quick the evaluate command would in comparison to just grabbing a cell’s value. But it adds value in that you don’t have to do anything with the actual spreadsheet beforehand.

As to using named ranges, I always find them a chore to update when the targetted range expands. Also, I’ve forgotten what it was but I think that applescript support for named ranges is missing some feature to manage them easily (or maybe just documented poorly).

Update on named ranges: As mentioned above, I’d forgotten the details but just thinking about this irked me so I re-opened the can of worms. My issue was about defining the named range in applescript. There is no ‘named range’ in the dictionary. Within the excel-2004 document, it tells you how to use a named range but never how to define one.

However, if you search for ‘named’ in the dictionary, you see a ‘named item’ property (of range) and just above it, ‘name’. Turns out you can define a named range using the ‘name’ property:

[format]name (text) : Returns or sets the name of the range[/format]

set name of range "D35:D66" to "round2player"

It has its own properties but you can also get the properties of the ‘named item’ of the given range along with the properties of the named range:

named item of range "D35:D66"
properties of named item of range "D35:D66"
properties of range "round2player"