Re: Merging Columns in Excel
Re: Merging Columns in Excel
- Subject: Re: Merging Columns in Excel
- From: David Hood <email@hidden>
- Date: Tue, 12 Feb 2002 22:12:21 +1300
Paul Berkowitz <email@hidden> wrote:
>
Is there a way to merge columns in Excel AppleScript? I'm told that
>
there is a way to do this in the UI, so I'm curious.
The only way I know of (using Excel 98) is pretty ugly. You need to know the
number of fields/columns in the file. Assuming I have two fields I want to
concatenate (Column1 and 2) I place the resulting concatenation in column 3:
tell application "Microsoft Excel"
Select Range "C3"
set FormulaR1C1 of Selection to "=RC[-2]&\" \"&RC[-1]"
CopyObject Selection
PasteSpecial Selection PasteType xlValues Operation xlNone without
SkipBlanks and Transpose
--PasteSpecial...Transpose is one line, beware email shortening
end tell
The PasteSpecial replaces the resultant formula with plain text, enabling the
source columns to be deleted, moved or modified without the concatenation being
affected.
While I script Excel a lot, it's for batch manipulation of data (generation of
multiple pivotTables and the like). A simple replace the tabs could be done with
anything supporting find replace (though you'd need to get grepish if only
replacing some of the tabs)
Regards,
David Hood
_/_/_/_/_/_/
Research Fellow, The Caversham Project, University of Otago
http://www.otago.ac.nz/nzpg/caversham
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.