Copy Excel cells from multiple files in a folder to new workbook

Scripting Forums AppleScript | Mac OS X
,
#1

I’m trying to create a script that opens each excel file in a folder, copies the contents of certain cells and then places them all in a single excel workbook in certain cells, with a different column for each source file.

Here is the script I currently have


tell application "Finder"
	set theFolder to folder "Macintosh HD:Users:agduncan:Documents:Sensory Lab:Month End:Feb:02 February"
	set theNumber to "29"
end tell
set columnCounter to "1"
repeat with i from 1 to theNumber
	set sourceFile to the name of item i of theFolder
	tell application "Microsoft Excel"
		activate
		open file sourceFile
		set cellValueA to value of cell "C7"
		set cellValueB to value of cell "C8"
		set cellValueC to value of cell "C9"
		set cellValueD to value of cell "C10"
		
		open file "Macintosh HD:Users:agduncan:Documents:Sensory Lab:Month End:Feb:02 February:target.xls"
		set value of cell in row "1" in column columnCounter to value of cellValueA
		set value of cell in row "2" in column columnCounter to value of cellValueB
		set value of cell in row "3" in column columnCounter to value of cellValueC
		set value of cell in row "4" in column columnCounter to value of cellValueD
		
		set columnCounter to columnCounter + 1
	end tell
end repeat

I have the following problems

  • The script does not always get values from cells in source files, often returning “”
  • I am getting error “Can’t get value of "".” number -1728 from «class DPVu» of “” from the set value lines for the target file.

Any pointers would be appreciated, thanks.

Operating System: Mac OS X (10.7)

#2

This is sloppy, but it should work.

property theColumns : {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD"}

tell application "Finder"
	set theFolder to folder "Macintosh HD:Users:agduncan:Documents:Sensory Lab:Month End:Feb:02 February"
end tell
tell application "Microsoft Excel"
	open "Macintosh HD:Users:agduncan:Documents:Sensory Lab:Month End:Feb:02 February:target.xls"
	repeat with i from 1 to 29
		set sourceFile to name of item i of theFolder
		open item i of theFolder
		delay 2
		set theData to value of range "C7:C10" of sheet "Sheet1" of workbook sourceFile
		set theRange to "" & item i of theColumns & "1:" & item i of theColumns & "4"
		set value of range theRange of sheet "Sheet1" of workbook "target.xls" to theData
		close workbook sourceFile without saving
	end repeat
end tell