Excel last cell
Excel last cell
- Subject: Excel last cell
- From: "Pete Willis" <email@hidden>
- Date: Wed, 13 Dec 2000 15:52:29 +0000
I am having problems getting the last cell of a large imported text file in Excel (by large I mean roughly 1000 rows by 18 columns so not massive). I found this script below by Carol Ellis dated Mon, 12 Apr 1999 on the archives which works fine on my text files as long as they are below 300 rows. With anything larger i get an error saying "unable to get length of 0". I presume that the line "set theSelection to FormulaR1C1 of theRange" is unable to process the large amount of data and just returns "0". Is there any other way to get the number of used rows as I can't seem to find one and I'm getting quite frustrated now. The Text file is a log report from a Splash RIP that I am trying to filter out data into a new excel sheet. Also what exactly is the definition of the FormulaR1C1 property as I can't find any documentation on the Excel dictionary.
set firstCellWasEmpty to false
tell application "Microsoft Excel"
set theFirstCell to FormulaR1C1 of Range "R1C1"
if theFirstCell is "" then
set FormulaR1C1 of Range "R1C1" to "a"
set firstCellWasEmpty to true
end if
set theRange to UsedRange of ActiveSheet of ActiveWorkbook
set theSelection to FormulaR1C1 of theRange
end tell
set lastRowUsed to length of theSelection
set lastColumnUsed to length of item 1 of theSelection -- just for your info
set theRange to "R" & (lastRowUsed + 1) & "C1"
tell application "Microsoft Excel"
set FormulaR1C1 of Range theRange to "your value here"
end tell
if (firstCellWasEmpty) then
tell application "Microsoft Excel"
set FormulaR1C1 of Range "R1C1" to ""
end tell
end if
by Carol Ellis on Date: Mon, 12 Apr 1999
A will do
Thanks
Pete Willis
Darcy Group UK