Re: Need Excel AppleScript for Office 2004 for Macintosh
Re: Need Excel AppleScript for Office 2004 for Macintosh
- Subject: Re: Need Excel AppleScript for Office 2004 for Macintosh
- From: Stan Cleveland <email@hidden>
- Date: Mon, 01 Oct 2007 14:54:00 -0700
- Thread-topic: Need Excel AppleScript for Office 2004 for Macintosh
On 10/1/07 1:59 PM, Raymond P Reedy wrote:
> Resend with correction. Range should be A1:AZ1
>
> This task is a little over my head with Microsoft Office 2004 and
> AppleScript.
>
> I have an Excel sheet with fifty-two columns and three hundred rows.
> Each row has been populated weekly, but not all rows received data.
> Thus I have empty cells in some rows. Thus A1 thru J1 looks like this
> for example...
>
> 50 25 empty 35 49 empty 29 42 empty 45
>
> and A2 thru J2 like this...
>
> empty 38 empty 25 46 38 empty 40 16 30
>
> What I need to do with the script is make A1 thru J1 look like this...
>
> 50 25 35 49 29 42 45 empty empty empty
>
> and A2 thru J2 like this...
>
> 38 25 46 38 40 16 30 empty empty empty
>
> In other words, shift cell data left until there are no empty cells
> between populated cells. I would like to do this with range A1:AZ1,
> stopping when A1 thru T1 (the left 20 columns) have been populated
> and ALL empty cells, if any, are at the right end of the row.
>
> Then when this works, I want to loop through all 300 rows.
>
> Way over my head. Any help would be appreciated.
Deivy's response is exactly right, but since you're "way over your head,"
here's some more specific help. The following code should get you started.
A quick explanation of the code follows. The first Excel command grabs all
the data for the entire range as a list of lists, one sublist per row. The
final Excel command writes the revised data back to Excel. In between, we
loop through the rows (i) and then the cells of each row (j). By stepping
through the cells backward, we can cleverly use "end of/beggining of" to
push the null strings to the end of the row data while keeping the data in
its original order. Since most of the logic uses plain AppleScript, and not
Excel, it will run very fast.
HTH,
Stan C.
set startRow to 1
set endRow to 300
tell application "Microsoft Excel"
set rowData to formula of range ("A" & startRow & ":M" & endRow)
end tell
set revisedData to {}
repeat with i from startRow to endRow
set thisRow to contents of item i of rowData
set revisedRow to {}
repeat with j from (count of thisRow) to 1 by -1
set thisCell to contents of item j of thisRow
if thisCell is "" then
set end of revisedRow to thisCell
else
set beginning of revisedRow to thisCell
end if
end repeat
set end of revisedData to revisedRow
end repeat
tell application "Microsoft Excel"
set formula of range ("A" & startRow & ":M" & endRow) to revisedData
end tell
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden