You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to automate a daily excel copy/paste job?

Current situation:


I get daily generated excel files with samples for each minute from the SCADA software for the equipment in my factory. These files consist of 10 columns and 1440 rows of data. To create a monthly file to analyze this data I copy and paste these 10C1440R into another file. This monthly file is used to show some KPI ( Key Performance Indicators ) for my factory. I've been doing this manually for quite some time now and it just screams out for Automator/AppleScript/FolderAction...


What I would like:


Have script run on a folder with a month worth of data and start with the first file, select the range I want, copy that range and paste it in the next empty cell in column A in my monthly file. Repeat for each file in the folder.


An alternative way could be a script performed on a newly added file to a folder. When I drop a new daily file on a folder the above described action should be performed on the file and added to the monthly file.


Can someone help me to make my life a bit more easy ;-)


JJ

iMac, OS X Mountain Lion (10.8.4), 27" late 2012

Posted on Sep 10, 2013 11:15 AM

Reply
15 replies

Apr 3, 2017 10:28 AM in response to twtwtw

@twtwtw


This is fantastic. I've been on a binge of using Automator / AppleScripts to automate tasks and this is exactly what I'm up against. That said, I'm trying to skip selecting the header on the source files as well as the first column. I've managed to figure out how to paste into the POSIX file starting on the second row:


-- write data into excel

set insertPoint to range ("A" & lastRowIndex + 2 & ":AC" & dataRowCount + lastRowIndex)


...but I still bring over the header when I copy from the original file(s).


tell used range of worksheet 1 of sourceBook

set copyData to items2 thru -1 of (get value)

set dataRowCount to count of copyData

set copyData to value

set dataRowCount to count of rows

end tell


Any suggestions?

Sep 10, 2013 6:10 PM in response to Jan Jaap Kikkert

This should get you 85% of the way, using the bulk run approach. Scripting Excel is always fidgety, and I haven't added any error handling, so you'll definitely need to tweak it on your end. also, it shouldn't be too difficult to convert to a folder action if you prefer that route.


set mainWorkbookPath to POSIX file "/path/to/final_workbook.xls"

set folderPath to "/path/to/folder of excel files"


tell application "System Events"


-- get files from folder

set fileList to POSIX path of (files of folder folderPath whose visible is true)

end tell


tell application "Microsoft Excel"

set mainBook to open workbookworkbook file namemainWorkbookPath


repeat with thisFile in fileList

set sourceBook to open workbookworkbook file name my (POSIX filethisFile)


tell used range of worksheet 1 of sourceBook

set copyData to value

set dataRowCount to count of rows

end tell


tell worksheet 1 of mainBook

tell used range


-- get info about used range of worksheet

set lastRowIndex to (count of rows)

if lastRowIndex = 1 then set lastRowIndex to 0

end tell



-- write data into excel

set insertPoint to range ("A" & lastRowIndex + 1 & ":K" & dataRowCount + lastRowIndex)

set value of insertPoint to copyData

end tell


closesourceBook

end repeat

end tell

Sep 10, 2013 8:32 PM in response to twtwtw

Thank you twtwtw,


It looks like the thing I need. However the source files contain some headers in the first rows. The actual data starts in cell A8. I think the bit below needs to be adjusted to skip the first 7 rows of data.


tell used range of worksheet 1 of sourceBook

set copyData to value

set dataRowCount to count of rows

end tell



How do I skip these first 7 rows and copy only the data starting from cell A8? It can't be a fixed range because sometimes datacollection crashes and I'm left with less than 1440 samples (rows). Range does always start at cell A8.


JJ

Sep 10, 2013 9:51 PM in response to Jan Jaap Kikkert

that's a minor tweak:


tell used range of worksheet 1 of sourceBook

set copyData to items 9 thru -1 of (get value)

set dataRowCount to count of copyData

end tell

Excel returns a standard applescript list of lists for the value of a range, so to skip the first 8 rows only use items starting with item 9. In applescript item -1 of a list is the last item (or technically, the first item from the end of the list).

Sep 10, 2013 10:01 PM in response to twtwtw

by the way, a spreadsheet with headers but no data (or a blank spreadsheet) will cause an error. If that's a likely problem you need to tweak it a bit more, like so:


tell used range of worksheet 1 of sourceBook

try

set copyData to items 9 thru -1 of (get its value)

on error

set copyData to {}

end try

set dataRowCount to count of copyData

end tell

and then 7 lines later, put the write data section inside an if block to skip empty lists:


if dataRowCount > 0 then


-- write data into excel

set insertPoint to range ("A" & lastRowIndex + 1 & ":K" & dataRowCount + lastRowIndex)

set value of insertPoint to copyData

end if

Sep 11, 2013 1:57 AM in response to twtwtw

Thank you twtwtw for your help!


But after running the script I end up with an empty final_worksheet.xls

The final_worksheet is opened and all the files in the folder are opened and closed but no data is copied (or pasted)


My script including your last tweak to skip empty cells:


set mainWorkbookPath to POSIX file "/Users/janjaap_old/Desktop/final_workbook.xls"

set folderPath to "/Users/janjaap_old/Desktop/X_July"



tell application "System Events"


-- get files from folder

set fileList to POSIX path of (files of folder folderPath whose visible is true)

end tell



tell application "Microsoft Excel"

set mainBook to open workbookworkbook file namemainWorkbookPath


repeat with thisFile in fileList

set sourceBook to open workbookworkbook file name my (POSIX filethisFile)


tell used range of worksheet 1 of sourceBook

try

set copyData to items 9 thru -1 of (get its value)

on error

set copyData to {}

end try

set dataRowCount to count of copyData

end tell


tell worksheet 1 of mainBook

tell used range


-- get info about used range of worksheet

set lastRowIndex to (count of rows)

if lastRowIndex = 1 then set lastRowIndex to 0

end tell


if dataRowCount > 0 then


-- write data into excel

set insertPoint to range ("A" & lastRowIndex + 1 & ":K" & dataRowCount + lastRowIndex)

set value of insertPoint to copyData

end if

end tell


closesourceBooksavingno

end repeat

end tell


Is there a way to step through a script to debug?

Sep 11, 2013 8:35 AM in response to Jan Jaap Kikkert

if you're running this from the script editor, click on the 'Events' button near the bottom of the window. That will show you step by step results of running the script. You can also add more information to the event log using the log command: i.e. log someVariable. if you copy part of the event log here I'll take a look at it. (all I need to see is the full cycle of handling one document)

Sep 11, 2013 9:54 AM in response to twtwtw

twtwtw,


the event window did the trick! Data was pasted in row 65000+ in the final_workbook file.

I created the final_workbook file from an existing file and deleted the data in it. Apparently there was some data left somewhere and new data was appended below this data. I created a new file from scratch and now it works like a charm!


You've made my life so much easier! I can't thank you enough.


JJ


EDIT: Next step is to persuade my company to switch to Mac OS X ;-)

Sep 11, 2013 12:19 PM in response to Jan Jaap Kikkert

twtwtw,


I've made some minor tweaks and I am one happy camper!


See final script below.


JJ


--I have created a script for each month. Files and folders for each month are there. I just need to copy daily files to the monthly folder and run srcipt for that specific month

set mainWorkbookPath to POSIX file "/Users/janjaap_old/Desktop/X_Aug.xls"

set folderPath to "/Users/janjaap_old/Desktop/X_Aug"



tell application "System Events"


-- get files from folder

set fileList to POSIX path of (files of folder folderPath whose visible is true)

end tell



tell application "Microsoft Excel"

set mainBook to open workbookworkbook file namemainWorkbookPath


repeat with thisFile in fileList

set sourceBook to open workbookworkbook file name my (POSIX filethisFile)


tell used range of worksheet 1 of sourceBook

try

set copyData to items 8 thru -1 of (get its value)


--changed 9 to 8. Using 9 started at second sample/row of data

on error

set copyData to {}

end try

set dataRowCount to count of copyData

end tell


tell worksheet 1 of mainBook

tell used range


-- get info about used range of worksheet

set lastRowIndex to (count of rows)

if lastRowIndex = 1 then set lastRowIndex to 0

end tell


if dataRowCount > 0 then


-- write data into excel

set insertPoint to range ("A" & lastRowIndex + 1 & ":J" & dataRowCount + lastRowIndex - 1)


--changed col K to J. In my language version of Excel both column "I" and "J" exists. Using J gives me 10 columns


-- last row of daily data is the first row of next day with only some of the cells populated hence the lastRowIndex -1

set value of insertPoint to copyData

end if

end tell


closesourceBooksavingno


--otherwise I would get a dialog to save or discard changes from Excel after each daily file is processed

end repeat


closemainBooksavingyes


--monthly files are created in advance and are opened from the script and then closed and saved after run

end tell


--many thanks again!

Sep 11, 2013 2:08 PM in response to Jan Jaap Kikkert

one more tweak I'd suggest, though it's by no means important. You could get the script to create the main workbook for you: basically, the script would ask you to select a month-folder and then would create the final workbook and process the files. Just change the beginning of the script to look like this:


-- choose a month-folder. path is returned as hfs rather than posix (colon rather than slash) because otherwise excel's 'save workbook as' command gets snippy

set folderPath to (choose folder with prompt "Choose a month folder") as text

tell application "System Events"


-- get files from month-folder

set fileList to POSIX path of (files of folder folderPath whose visible is true)


-- get name of month-folder

set monthFolderName to name of folderfolderPath

end tell


-- add .xls to folder path so that the main workbook is created outside the folder being processed

set MainBookFilePath to (text 1 thru -2 of folderPath) & ".xls"


tell application "Microsoft Excel"


-- the following line will give errors if the monthFolderName.xls file already exists or a monthFolderName workbook is already open. forgive Microsoft it's weird implementation and horrible syntax, if you can...

save workbook as (makenewworkbook) filenameMainBookFilePath

set mainBook to workbookmonthFolderName

Sep 11, 2013 2:55 PM in response to twtwtw

twtwtw,


Thanks again for your help!


The creation of the monthly file is a small job but since you seem to want to improve my script ;-) what I would like is to move already imported files from the monthly folder to a folder "X_month_already_imported". This would enable me to drop new files on the monthly folder and only new files would be processed and added to the monthly file. Instead of moving the files already imported deleting them is also an option. Original files will always reside on my workgroup drive at my work so no need for safety measures.


JJ

Sep 11, 2013 3:18 PM in response to Jan Jaap Kikkert

easy enough. add the following handler to the script:


on moveFinishedFile(f)

tell application "System Events"

move file f to "/path/to/folder X_month_already_imported"

end tell

end moveFinishedFile


then call it right after you close each source file:


closesourceBooksavingno



-- call the move file handler

my moveFinishedFile(thisFile)

(the my keyword is needed because you're calling the handler from within an Excel tell block)

Nov 19, 2013 6:49 PM in response to twtwtw

Thanks! With some minor tweaking, I managed to get mines working the way I want. I just picked up applescript 3 days ago and working with excel is extremely hard for me. >,< Things just seem to break and go wrong everywhere and I don't understand why. Debugging is also a chore with only the event log to help me. Applying your script has helped me to understand it much better. Enough for me to start doing other things in excel at least.


Thanks again!

How to automate a daily excel copy/paste job?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.