I am building a script that grabs data from specific cells in several Excel workbook files (I intend for ~50 separate files to be dropped on the file at once) and pastes that data into one target file. After all of the data is pasted a user can easily tabulate the data within the one target file. Problem: the repeat loop can copy the data from the specified cells fine, nothing show in the target file. The script event log indicates that the script worked, it seems to be labeling the target cell with the data instead of pasting it is. Thoughts??
tell application "Microsoft Excel"
set theTargetFile to "Work:Users:mcreynos:Desktop:Target-File.xls"
open theTargetFile
set theFiles to (choose file with multiple selections allowed)
repeat with i from 1 to count of theFiles
set myFile to open (item i of theFiles)
set num to i
copy value of cell "B1" of (item i of theFiles) to csName
copy value of cell "F11" of (item i of theFiles) to satHours
copy value of cell "F12" of (item i of theFiles) to sunHours
set csName to value of cell ({"A", num} as string) of worksheet of file theTargetFile
set satHours to value of cell ({"B", num} as string) of worksheet of file theTargetFile
set sunHours to value of cell ({"C", num} as string) of worksheet of file theTargetFile
end repeat
end tell
try like this
set value of cell ({"A", num} as string) of worksheet of file theTargetFile to csName
set value of cell ({"B", num} as string) of worksheet of file theTargetFile to satHours
set value of cell ({"C", num} as string) of worksheet of file theTargetFile to sunHours
It’s setting the values of the cells within the chosen files to the pulled data. Instead of within the target file. Is the problem with the the repeat loop? Perhaps taking “set value of cell.the target file” out of it?
Hi,
your copy and set statements are a bit confusing, 
the set lines are in the wrong order.
Try this:
tell application "Microsoft Excel"
set theTargetFile to "Work:Users:mcreynos:Desktop:Target-File.xls"
open theTargetFile
set TargetFileName to name of active workbook
set theFiles to (choose file with multiple selections allowed)
repeat with i from 1 to count of theFiles
open (item i of theFiles)
set myFile to name of (info for item i of theFiles)
set num to i as string
tell worksheet 1 of workbook myFile
set csName to value of cell "B1"
set satHours to value of cell "F11"
set sunHours to value of cell "F12"
end tell
tell worksheet 1 of workbook TargetFileName
set value of cell ("A" & num) to csName
set value of cell ("B" & num) to satHours
set value of cell ("C" & num) to sunHours
end tell
end repeat
end tell
Give this a go, I tested it with 3 source files and does what your looking for (I think :P)
set csName to {}
set satHours to {}
set sunHours to {}
set theFiles to (choose file with multiple selections allowed)
tell application "Microsoft Excel"
repeat with aFile in theFiles
open aFile
copy value of cell "B1" to end of csName
copy value of cell "F11" to end of satHours
copy value of cell "F12" to end of sunHours
close active workbook saving no
end repeat
set deskpath to path to desktop as Unicode text
set theTargetFile to deskpath & "Target-File.xls"
open theTargetFile
repeat with i from 1 to (count theFiles)
set value of cell ({"A", i} as string) to (item i of csName)
set value of cell ({"B", i} as string) to (item i of satHours)
set value of cell ({"C", i} as string) to (item i of sunHours)
end repeat
close active workbook saving yes
end tell
the scripts work great, thanks so much!! One more question. The excel files I’m working with are linked to another excel file that no long exists. So everytime you open the files a window pops up asking if you want to update the miss link and you hit cancel. How can this be added to the script?
I’m about 99% positive you would have to use GUI scripting to do that, but I’m afraid my knowledge of excel is limiting me on this. I managed to create links, but not that produced a dialog box similar to what your talking about.
So I would either start looking at GUI Scripting or if you could tell me how to create a link similar to what exactly you have going on so I can test it let me know.
What’s about creating a dummy Excel file with the expected name or remove the dead links?
I’m working with hundreds of individual Excel files that I need to pull data from. Hence the applescript. I thought GUI may be the only way to do this. I have my hierarchy for the GUI set in place I just need to figure out how that is placed into the existing script.
See below
set csName to {}
set satHours to {}
set sunHours to {}
set theFiles to (choose file with multiple selections allowed)
tell application "Microsoft Excel"
repeat with aFile in theFiles
open aFile
(*
This is where the linked dialog should be coming up
===============================
tell application "System Events"
tell process "Excel"
-- insert GUI Scripting statements here
end tell
end tell
===============================
*)
copy value of cell "B1" to end of csName
copy value of cell "F11" to end of satHours
copy value of cell "F12" to end of sunHours
close active workbook saving no
end repeat
set deskpath to path to desktop as Unicode text
set theTargetFile to deskpath & "Target-File.xls"
open theTargetFile
repeat with i from 1 to (count theFiles)
set value of cell ({"A", i} as string) to (item i of csName)
set value of cell ({"B", i} as string) to (item i of satHours)
set value of cell ({"C", i} as string) to (item i of sunHours)
end repeat
close active workbook saving yes
end tell