Excel 2008 - how to get a column's range of used cells.

Scripting Forums AppleScript | Mac OS X
,
#1

Hello. (after posting i realized the subject heading was misleading… should have been “how can i get…” sorry if anybody opened it hoping for an answer)

I’ve been working on a script and seem to have hit a wall with the final piece of it.

I am taking data from excel, processing it in bbedit, bringing it back into excel, sorting by the column i just pasted into, and pasting the relevant data into the previous column.

the problem i’m having is how to copy the cells in column C that actually have data in them and paste them to column B without erasing data from those cells that don’t have corresponding data in column C.

example…

A B C
1 2 3
1 2 3
1 2 3
1 2
1 2

becomes…

A B C
1 3 3
1 3 3
1 3 3
1 2
1 2

at which point i will delete column C.

here’s what i have working so far (i’m including the BBedit content as well). I’m sure it can be cleaned up a bit, but right now i’m just trying to get it functional.

any help would be greatly appreciated. The things i’ve found online that SHOULD work don’t seem to…

tell application "Microsoft Excel"
	open text file filename ¬
		"Macintosh HD:Users:me:testfile.csv" data type delimited with comma
	copy range column "V:V"
end tell
tell application "BBEdit"
	activate
	make new text document
	paste
	replace "-" using "," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	replace "win," using "win,\\r" searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	replace "pass," using "\\rpass," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	process lines containing text 1 of text document 1 matching string "win," output options {deleting matched lines:true, reporting results:false}
	process lines containing text 1 of text document 1 matching string "pass," output options {deleting matched lines:true, reporting results:false}
	replace ", " using "" searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	replace "," using "\\r," searching in text 1 of text document 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}
	process lines containing text 1 of text document 1 matching string "," output options {deleting matched lines:true, reporting results:false}
	select text 1 of text window 1
	copy selection
	close active document of window 1 saving no
end tell
tell application "Microsoft Excel"
	activate
	insert into range column "C:C" of worksheet 1 of active workbook shift shift to right
	select range "c1" of worksheet 1 of active workbook
	paste worksheet sheet 1 of active workbook destination range "C:C"
	sort range "C1" order1 sort ascending key1 column "C:C" header header yes without match case
end tell
#2

Hi,

Hopefully, if I’ve understood your request correctly, this should do the trick!!!

tell application "Microsoft Excel"
	activate
	sort range "C1" order1 sort ascending key1 column "C:C" header header yes without match case
	set cell_No to 1
	set cell_contents to "not empty"
	repeat until cell_contents is ""
		set cell_contents to value of cell ("C" & cell_No)
		if cell_contents is not "" then
			set value of cell ("B" & cell_No) to cell_contents
		end if
		set cell_No to cell_No + 1
	end repeat
end tell

Thanks,
Nik

#3

that worked very well. it’s a different approach than i was expecting but quite effective (it was fun to watch the values change one at a time while the script ran… i’m apparently easily amused).

getting this script fully functional is going to finally allow me to delegate this nightly responsibility to the monkeys we have working in the office at night… a few minor things to work out and it should be idiot-proof and i’ll get my evenings back. woohoo!

i thank you and my social life thanks you. : )

-david