Simple "fill down" and "format data" Excel Script Help!

Scripting Forums AppleScript | Mac OS X
,
#1

I get a spreadsheet of order data daily that varies in the number of records, but the column structure is always the same. For each spreadsheet, I have to add a return address, that is again, always the same. I’ve come up with a way to place the column name and first row of data in the appropriate place: (names changed to protect the innocent)


tell application "Microsoft Excel"
	tell cell "AC1" to set value to "upsaccount"
	tell cell "AC2" to set value to "XXXXXX"
	tell cell "AD1" to set value to "upscompany"
	tell cell "AD2" to set value to "XXXXXX"
	tell cell "AE1" to set value to "upsaddress1"
	tell cell "AE2" to set value to "XXXXXX"
	tell cell "AF1" to set value to "upsaddress2"
	tell cell "AF2" to set value to "XXXXXX"
	tell cell "AG1" to set value to "upscity"
	tell cell "AG2" to set value to "New York"
	tell cell "AH1" to set value to "upsstate"
	tell cell "AH2" to set value to "New York"
	tell cell "AI1" to set value to "upszip"
	tell cell "AI2" to set value to "10014"
	tell cell "AJ1" to set value to "upscountry"
	tell cell "AJ2" to set value to "USA"
	tell cell "AK1" to set value to "srpmethod"
	tell cell "AK2" to set value to "Ground"
	tell cell "AL1" to set value to "billingoption"
	tell cell "AL2" to set value to "Third Party"
	tell cell "AM1" to set value to "returncompany"
	tell cell "AM2" to set value to "XXXXXX"
	tell cell "AN1" to set value to "returnname"
	tell cell "AN2" to set value to "XXXXXX"
	tell cell "AO1" to set value to "returnaddress1"
	tell cell "AO2" to set value to "XXXXXX"
	tell cell "AP1" to set value to "returnaddress2"
	tell cell "AQ1" to set value to "returncity"
	tell cell "AQ2" to set value to "Keasbey"
	tell cell "AR1" to set value to "returnstate"
	tell cell "AR2" to set value to "NJ"
	tell cell "AS1" to set value to "returnzip"
	tell cell "AS2" to set value to "08832"
end tell

I face two problems I cannot find solutions for. I need to “fill down” the address to every row that is already populated. Secondly, I need to find a way to format the text in column “AS” to zip code or 5-digit number “00000”

#2

I don’t use Excel, so I’m basing this off the Excel 2004 dictionary I found online and I can’t test it for you, but hopefully it at least gets you on the right track.

autoformat range (column "AS:AS") without number --(replace this line if needed)
set topCells to every cell of range "AC2:AS2"
repeat with topCell in topCells
	set thisValue to value of topCell
	set thisColumn to (cells 3 through -1) of (entire column of range topCell)
	repeat with thisCell in thisColumn
		set value of thisCell to thisValue
	end repeat
end repeat

That should do it if I understand Excel’s dictionary correctly. If the ZIP code formatting doesn’t work, try replacing the words “without number” to “format range autoformat none” or “format range autoformat simple”. One of those ought to do the trick. Also, this line probably needs to be moved to somewhere in your script before you assign your zip code to the value of cell AS2 to work properly.

If that doesn’t work at all, here’s the same script as it’s written in Numbers. It’s possible Excel now supports a table structure closer to this:

tell application "Numbers" to tell front document to tell sheet 1 to tell table 1
	set format of column 48 to «constant ****----»
	set rowCount to count of rows
	--add these lines if your table contains blank rows below the ones you want filled
	repeat with i from 2 to rowCount
		if value of cell 2 of row i is 0 then
			set rowCount to i - 1
			exit repeat
		end if
	end repeat
	--skip to here if every row of your table should contain data
	repeat with thisColumn from 29 to 48
		set thisValue to value of cell thisColumn of row 2
		repeat with thisRow from 3 to rowCount
			set value of cell thisColumn of row thisRow to thisValue
		end repeat
	end repeat
end tell

This script also addresses the possibility that you have bank rows that you don’t want filled after your data ends. You should be able to do something similar in Excel, but I don’t know how Excel assigns value to an empty cell. (It could be 0, “”, “0.0”, etc.)