This script copies from one document to another and often has problems in the paste funtion. I hope to clear up the problem by clearing cells before the paste command. I also want to make sure it pastes into the rows that I want it to.
It stops at my clear command. Advice?
on run
idle
end run
on idle
tell application "Finder" to set t_file to (duplicate "Big Disk:Other:WordCounts.xls" to "Macintosh HD:wcounts" with replacing) as Unicode text
tell application "Microsoft Excel"
open t_file
set oldValue to display alerts
set display alerts to false
save workbook "WordCounts.xls" in t_file as Excel9795
tell application "Microsoft Excel"
set display alerts to false
copy range range "A1:D1800" of the active sheet
end tell
close workbook "WordCounts.xls"
end tell
tell application "Microsoft Excel"
open "Macintosh HD:wcounts:WCSlave14dz2.xls"
set oldValue to display alerts
set display alerts to false
tell application "Microsoft Excel"
activate object worksheet "Details"
select (range "a1:d1600")
clear (range "a1:d1600")
paste worksheet active sheet
end tell
tell application "Microsoft Excel"
tell sheet "Details"
run VB macro "Sortdescending"
end tell
set oldValue to display alerts
set display alerts to false
tell application "Microsoft Excel"
run VB macro "Formatting"
end tell
tell application "Microsoft Excel"
save workbook "WCSlave14dz2.xls"
set display alerts to oldValue
end tell
end tell
close workbook "WCSlave14dz2.xls"
end tell
return 15 * 15 -- Every 15 minutes in seconds
end idle
Hi dzelnio. I’m not an expert in excel at all, but I’ve had problems with excel if I don’t give it enough time to do the commands. I found that a sprinkling of delay commands can help. I notice you’re copying a large number of cells. That might take more time than the script is allowing for. When the script stops, you can check the clipboard to make sure a copy occurred. Also, I notice you’re selection range does not match the range you copied. I’m not sure if there’s a reason for that, but you might want to make those dimensions the same. The selection process itself might need a delay as well. If the script is getting to the clear command before excel has had time to actually make the selection, it wouldn’t clear anything. Just some thoughts I had . . . might consider it.
Truth is dzelnio, I didn’t use a delay statement in the excel stuff I have left lying around. I was putting data into cells one by one without selecting the cell and therefore didn’t need the delay. But here’s a subroutine I wrote to click a link on a Safari web page. The way I made it work is by looking to see that the reload button was back to reloading position and that the address and page title had actually changed. (I found that without checking the web address when Safari was really slow, the routine would think the new page had loaded even before it had started.) I’m sure somebody’s got a better way to do this, but this has the delay commmand in it so that the routine will give Safari time to get the job done.
to clickWaitVerifyInSafari(clickPosition, numberKillPasses, sendKillMessage)
-- Independent
(* clickPosition is a list with the first item the x coordinate and the second the y coordinate
numberKillPasses is the number of 2 second time intervals to wait to verify the new web page
sendKillMessage is true for the KillMessage to be sent if successFlag is false
*)
tell application "Safari"
activate
tell application "System Events"
tell process "Safari"
set oldWebAddress to value of text field 1 of splitter group 1 of group 4 of tool bar 1 of window 1
set newWebAddress to oldWebAddress
set oldTitle to name of static text 1 of window 1
set newTitle to oldTitle
set oldHelp to help of button 1 of group 2 of tool bar 1 of window 1
set newHelp to ""
click at clickPosition
set i to 1
repeat until (newWebAddress ≠oldWebAddress and newTitle ≠oldTitle and newHelp = oldHelp) or i = numberKillPasses
delay 2
set i to i + 1
set newWebAddress to value of text field 1 of splitter group 1 of group 4 of tool bar 1 of window 1
set newTitle to name of static text 1 of window 1
set newHelp to help of button 1 of group 2 of tool bar 1 of window 1
end repeat
set successFlag to true
if i = numberKillPasses then set successFlag to false
end tell -- process "Safari"
end tell -- application "System Events"
if successFlag is false and sendKillMessage is true then
set killMessage to "Sorry must have lost the connection or something. Couldn't connect."
display dialog killMessage buttons {"Cancel"}
end if -- successFlag is false
end tell -- "Safari"
set output to successFlag
end clickWaitVerifyInSafari
Just in case you’re interested, I avoided selecting the individual Excel cells by using code like this:
.
.
.
set cellStr to convertRowColumnNumbersToDollarSignFormat(j, i)
setDataFromAnExcelCell(cellStr, shName, wbName, item k of validWordList)
end repeat -- with k
setDataInAnExcelCell("A1", shName, wbName, i)
setDataInAnExcelCell("B1", shName, wbName, j)
end putWordsIntoLiteratiWordIndex
to getDataFromAnExcelCell(cellStr, shName, wbName)
-- Independent
tell application "Microsoft Excel"
set output to value of cell cellStr of sheet shName of workbook wbName
end tell -- "Microsoft Excel"
end getDataFromAnExcelCell
to setDataInAnExcelCell(cellStr, shName, wbName, cellData)
-- Independent
tell application "Microsoft Excel"
set value of cell cellStr of sheet shName of workbook wbName to cellData
end tell -- "Microsoft Excel"
end setDataInAnExcelCell
The ‘setDataInAnExcelCell’ would be the part most interesting to you. But with your amount of data, I would probably go the route you’re taking and throw in delays at key points. In testing usually I set the delay at 5 or 6 seconds just to see what’s going on. Then I reduce it until it the code starts to fail. Then I double its value to take care of most of the statistical fluctuation.
. . . might consider it.