I have written a script that reads data from a closed excel file then copies to clipboard and pastes into a new excel workbook. This works great. But I want this to action to be repeated every 5seconds.
The original closed excel file is made by a piece of software that continually writes to it with new data, so I want to be able to retrieve this new data as it comes in. So I figured a repeat loop in my script would be best, however I am having a little trouble, it seems to source the file again but doesn\t pick up any changes.
Heres my script so far, any help would be great, thanks in advance
set destFile to ((path to desktop as text) & “data.xls”)
read alias ((path to desktop as text) & “data_raw.xls”)
set the clipboard to result
tell application “Microsoft Excel”
close every window
open destFile
tell sheet “Sheet1” of workbook “data.xls”
paste worksheet
end tell
end tell
paste worksheet is a single command (not a command + argument) that pastes the clipboard onto the indicated worksheet, the sheet named “Sheet1” in this case.
The OP might try copying of data_raw inside the tell, rather than using the read proceedure.
Running the posted code every 5 seconds would mean that “Data.xls” would be opened and closed every 5 seconds. That’s awfuly quick, it might take longer than 5 seconds for Excel to open/close the file in that time interval.
This may not be the right way of doing it, but it does actually work, this is only my 2nd attempt at scripting!
I dont want the file to be closed and reopened again, just the new data added so that I can make further calculations from it.
I set it to read that file, as the software writing to the source file doesn’t like it when it is open.
So I want to be able to read the data from this source file at regular intervals, if 5seconds is too short then this can be changed or set to a key stroke to update, and paste this data into data.xls file so that I can make further calculations from it.
I originally connecting to external data within excel but this did not prove fruitful
Any suggestions gratefully received.
try
set thePath1 to path to desktop folder as string
set theFile1 to thePath1 & “source doc.xls” as alias
open for access theFile1
set fileContents to (read theFile1)
set thePath2 to path to desktop folder as string
set theFile2 to thePath2 & “destination doc.xls” as alias
set theFile2 to open for access theFile2 with write permission
set eof theFile2 to 0
write fileContents to theFile2
close access theFile2
end try
This will write the complete contents of the source file to the destination file (overwriting what’s there). Perhaps someone can help you write just the new info to the end of the destination file).
It can then be run via a keyboard shortcut, or have repeat, delay 5, end repeat added.
Hi Kev
thanks for the reply, gave it a shot, but it only seemed to put UB in cell A1 not the data that was there, and it wouldn’t refresh when the destination file was opened. Thats what I need to be able to do
OK, just had another go, and this definitely works:
property thePath1 : path to desktop folder as string
property theFile1 : thePath1 & “source_doc.xls” as alias
property thePath2 : path to desktop folder as string
property theFile2 : thePath2 & “destination_doc.xls” as alias
try
open for access theFile1
set fileContents to (read theFile1)
close access theFile1
open for access theFile2 with write permission
set eof theFile2 to 0
write fileContents to theFile2
close access theFile2
end try
Sorry about the last one (must remember to test more thoroughly).
