Excel 2008 - copying data up and down columns, used ranges, etc.

Scripting Forums AppleScript | Mac OS X
,
#1

Hello.

I have been monkeying around with some more scripting in Excel 2008 and have come up with some problems i can’t figure out.

In this first part I would like to find the used range of the document and autofill row numbers all the way to the final row. For now I put in a number manually and it does exactly what i’d like for it to do automatically.

tell application "Microsoft Excel"
	activate
	insert into range column "A:C" of worksheet 1 of active workbook shift shift to right
	set value of cell ("A1") to 1
	set value of cell ("A2") to 2
	autofill range "A1:A2" destination range "A1:A3760"
	sort range "D1" order1 sort ascending key1 column ¬
		"D:D" header header no without match case
end tell

one thing that i would like to add to the above bit of code is for the script to move any dates from column D over to column B. If i could also “find” a text string (e.g. “Totals for vehicle nbr:”) in column D and move the appropriate cells over to column C that would be magical.

The next part also works, but errors out at the end with “Microsoft Excel got an error: The object you are trying to access does not exist”

the object is to start from the bottom and copy each subtotal’s label to its respective line items. The error pops up when the top of the document is reached. I’d like to be able to use the “used range” to tell it where to start and stop.

tell application "Microsoft Excel"
	activate
	set cell_No to 3760
	set cell_dest to "not empty"
	set cell_source to "not empty"
	repeat until cell_No is "1"
		set cell_source to value of cell ("C" & cell_No)
		set cell_dest to value of cell ("C" & cell_No - 1)
		if cell_dest is "" then
			set value of cell ("C" & cell_No - 1) to cell_source
		end if
		set cell_No to cell_No - 1
	end repeat
end tell

The next part is the same thing in reverse. It’s copying the dates down into the individual line items. The problem with this piece is that it doesn’t stop when it gets to the row i tried to tell it to stop at. As above i’d like to be able to use the “used range” to set the start and end points.

tell application "Microsoft Excel"
	activate
	set cell_No to 1
	set cell_dest to "not empty"
	set cell_source to "not empty"
	repeat until cell_No is "3760"
		set cell_source to value of cell ("B" & cell_No)
		set cell_dest to value of cell ("B" & cell_No + 1)
		if cell_dest is "" then
			set value of cell ("B" & cell_No + 1) to cell_source
		end if
		set cell_No to cell_No + 1
	end repeat
	end tell

the final bit is just a question of whether the “sort” command at the beginning is necessary or not…

tell application "Microsoft Excel"
	delete range column "A:A" shift shift to left
	sort range "C1" order1 sort ascending key1 column "C:C" header header no without match case
	try
		delete range (entire row of (special cells of range "C:C" type cell type blanks)) shift shift up
	end try
end tell

If i want to enter the variables manually it all works, but I’m sure someone knows a better way.

any help anybody can give would be very much appreciated.

-david

#2

In Re: “final bit”
What happened when you tried it?

Unless you require the result of the routine to be sorted, there is no need to sort before deleting.

In Re: the other parts, have you looked at the property Used Range?

#3

Ahhh… back home on my Mac. (Away from the nasssty PC at work)

Used Range is an unreliable measure for the end of data. For example if an entire column is formatted, then that entire column will be part of the Used Range. The method FinalRowOfColumn below is one way to get the last data cell in a column. (Note that the columnIndex argument is numeric and that a cell containing a formula that returns “” will be in the range.)

The first script can be replaced with

tell application "Microsoft Excel"
	set finalRow to my FinalRowOfColumn(1)
	
	insert into range (get resize range "A1" row size finalRow column size 3) shift shift to right
	
	tell (get resize range "A1" row size finalRow)
		set formula to "=row()"
		set value to get value
	end tell
end tell


on FinalRowOfColumn(columnIndex)
	try
		tell application "Microsoft Excel"
			return first row index of (get end of (cell (count of rows) of column columnIndex) direction toward the top)
		end tell
	on error
		return 0
	end try
	
end FinalRowOfColumn

This is very useful for looping, the second script could be written as this.
Note that it looks to column A to see where the last row is, but alters the contents of column B.

tell application "Microsoft Excel"
	activate
	set finalRow to my FinalRowOfColumn(1)
	set cell_dest to "not empty"
	set cell_source to "not empty"
	repeat with cell_No from finalRow to 1 by -1
		set cell_source to value of cell ("B" & cell_No)
		set cell_dest to value of cell ("B" & cell_No + 1)
		if cell_dest is "" then
			set value of cell ("B" & cell_No + 1) to cell_source
		end if
	end repeat
end tell

on FinalRowOfColumn(columnIndex)
	try
		tell application "Microsoft Excel"
			return first row index of (get end of (cell (count of rows) of column columnIndex) direction toward the top)
		end tell
	on error
		return 0
	end try
end FinalRowOfColumn

Although you could try a non-looping script

tell application "Microsoft Excel"
	activate
	set finalRow to my FinalRowOfColumn(1)
	try
		tell (special cells (get resize range "b2" row size finalRow - 1) type cell type blanks)
			set formula to "=R[-1]C"
			set value to get value
		end tell
	end try
end tell

on FinalRowOfColumn(columnIndex)
	try
		tell application "Microsoft Excel"
			return first row index of (get end of (cell (count of rows) of column columnIndex) direction toward the top)
		end tell
	on error
		return 0
	end try
end FinalRowOfColumn
#4

Mike,

What happens in cases where you are only interested in the last row of data, regardless of which column it’s in? I am trying to copy and paste data from multiple sheet tabs into sheet 1 in a vertical fashion. So, for example:

-on sheet 1, I need to go to the last row of data, move down one more cell, then activate that last cell in column A.
-copy all data from sheet 2 and paste into that active cell on sheet 1, move down again to last active row…
-copy data from sheet 3, repeat…

I am able to maneuver between the sheets like this:


tell application "Microsoft Excel"  --> this sets the current date to a1 cells to sheets beyond sheet 1
	activate
	set theSheets to sheets of active workbook
	repeat with nextSheet from 2 to (count theSheets)
		if (count theSheets) < 2 then
			display dialog "no sheets"
			exit repeat
		else
			activate object sheet nextSheet
			set value of range "a1" to (current date)
		end if
	end repeat
	
	activate object sheet 1

end tell

THanks,
Marlon

#5

Hi
try this, modified last year from a script somewhere in this site.

tell application "Microsoft Excel"
	launch
	set sheetlist to {worksheet "Sheet2" of active workbook, worksheet "Sheet3" of active workbook}
	repeat with i from 1 to count sheetlist
		set thisheet to item i of sheetlist
		activate thisheet
		set usedRange to used range of thisheet
		activate object worksheet "Sheet1"
			if get value of range "A1" is "" then
			set FoundRange to "A1"
		else
			set TargetRange to column 1 of the active sheet
			set AfterCell to cell "A1" in the active sheet
			set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction search next
		end if
		copy range usedRange destination FoundRange
	end repeat
end tell

hope it helps
bills

#6

Thank you Bills,

This will get me started in the right direction. I will report back how this pans out.

Marlon