Apple

Le 21/08/2013 à 14:02, Willard L. McMunn <email@hidden> a écrit :

Try this:

tell application "Microsoft Excel"
tell active workbook
tell active sheet
set theLastRow to first row index of (last cell of used range)
tell range ("A1:A" & theLastRow)
set theResult to string value of every cell
repeat with jItem from 1 to count theResult
if item jItem of theResult = "" then
set item jItem of theResult to false
end if
end repeat
set theResult to strings of theResult
end tell
end tell
end tell
end tell

Thanks
but

(1) what need for range ("A1:A" & theLastRow)
when range ("A:A")
is supposed to behave the same ?

From my experience it doesn't. Excel can't handle

set theResult to string value of every cell of range ("A:A")

it will just make Excel use every cycle of CPU available and eventually give a time out for the script (you still have to kill Excel yourself).

(2) As I wrote in an other message, dropping the content of empy cells is asked because I am curious.
Using a loop is a trivial soluce.
I am in search of a more elegant one.
From my point of view, the whose filter was the perfect one but it's not available in XL.
It's why I asked twice if :
# these two instructions aren't speaking to Excel so there is no need to put them in the tell block
set testFile to choose file "PLease select the Excel Workbook!"
set fileName to testFile as string
tell application "Microsoft Excel"
	set newWorkbook to open workbook workbook file name fileName
	tell sheet 1 of newWorkbook
		# the cell B1 is supposed to be empty
		set theRange to (column difference range ("A:A") comparison range "B1")
		set theResult to (value of theRange)
	end tell
end tell
is behaving flawlessly.
This code is based upon what was already posted here and what I read in Excel2004 AppleScript Ref.pdf

When I tested it, I couldn't get the "column differences" to work:

set theRange to column differences range "A1:A10" comparison range "B1"

gives the error:

error "„Microsoft Excel“ hat einen Fehler erhalten: „range \"A1:A10\" of sheet 1 of workbook \"Skript test data.xlsx\"“ versteht die Nachricht „column differences“ nicht." number -1708 from range "A1:A10" of sheet 1 of workbook "Skript test data.xlsx"

(the range "A1:A10" of sheet 1 doesn't understand the event „column differences“).

The only way I got the comparison going was using a cell in the same range:

set theRange to column differences range "A:A" comparison range "A1000"

But then this range is a stranger beast than the other ranges, in my example it looks like

range "'[Skript test data.xlsx]143,01'!$A$1:$B$8;$A$10:$A$30;$A$32;$A$35:$B$36;$A$38:$B$39;$A$40:$A$44"

(no idea where the "B"s come from) and then trying

set theResult to string value of every cell of theRange

throws an error

error "„Microsoft Excel“ hat einen Fehler erhalten: The object you are trying to access does not exist" number -1728 from string value of every cell of range "'[Skript test data.xlsx]143,01'!$A$1:$B$8;$A$10:$A$30;$A$32;$A$35:$B$36;$A$38:$B$39;$A$40:$A$44"

I didn't get any further.

Best

Thomas

References:
	>Re: AS and Excel (From: "Willard L. McMunn" <email@hidden>)
	>Re: AS and Excel (From: "koenig.yvan" <email@hidden>)