Apple

Le 24/02/2014 à 12:13, Thomas Fischer <email@hidden> a écrit :

Hi Bert,

it took a me while to get back to your question.
I still have problems to get at the coordinates of Excel ranges inside of AppleScript, but the script below should get rid of empty lines within the used range of an Excel sheet.
As in your example it works on the current active sheet open in Excel.

tell application "Microsoft Excel"
set sheetData to value of used range of active sheet -- grab all data from Excel
set newData to {}
set emptyData to {}
repeat with theRow in sheetData
if theRow as string ≠ "" then
copy theRow to the end of newData
else
copy theRow to the end of emptyData
end if
end repeat
set the value of used range of active sheet to newData & emptyData
end tell

By the way, the 192 lines come from the used range:
get areas of used range of active sheet
--> {range "'[Folder_08 copy.xls]Blatt1'!$A$1:$V$192"}
I didn't figure out in which sense the last empty lines are "used".

Best
Thomas

Am 21.02.2014 um 13:39 schrieb Bert Groeneveld:

Hi Thomas, thank you for your reaction,

I have a workflow in which an applescript processes Excel documents. Sometimes the Excel document contains empty rows. I want to get rid of all empty rows. I guess there must be some "simple" command in the Excel Applescript dictionary to remove empty lines. I can't figure it out so I wrote following workaround script:

tell application "Microsoft Excel"
set sheetData to value of used range of active sheet -- grab all data from Excel

-- repeat loop to delete all empty rows:
set temp_sheetdata to {}
repeat with r from 1 to (length of sheetData)
set thisRow to item r of sheetData
if thisRow as string ≠ "" then
set the end of temp_sheetdata to thisRow
end if
end repeat

-- clear entire contents of Excel document:
set numberofrows to count rows of used range of active sheet
repeat numberofrows times
delete row 1 of active sheet
end repeat

-- set new range:
set used_range to "A1:V" & (length of temp_sheetdata) as string -- I know that column V is always the last column

set value of range used_range of active sheet to temp_sheetdata -- push all data back to Excel
end tell

I attached an example Excel file. Strange thing about this Excel file with 143 rows is that when I (manually) save it as a tab delimited text file I get 192 lines of text (instead of the expected 143). I can't find out where the additional 49 lines come from. Fortunately, my workaround script fixes this :-)

I hope to find out if there is a smarter way to delete empty rows from an Excel document. That's why I post it on the mailing list.

Bert.

References:
	>How can I delete every empty row of an Excel document? (From: Bert Groeneveld <email@hidden>)
	>Re: How can I delete every empty row of an Excel document? (From: Thomas Fischer <email@hidden>)
	>Re: How can I delete every empty row of an Excel document? (From: Thomas Fischer <email@hidden>)