How to automate a daily excel copy/paste job?

@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?

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

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).

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

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)

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

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)

no, it's a handler (like a subroutine, or a function, or a method, or a Shakespearean aside). Tack it on to the end of the script, then insert that one line that calls it after the close command and before the end repeat line.

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!