I’m writing a script that will take a folder of excel files (about 4 files total), use a repeat loop to go thru each one and determine the area of content to copy via the first empty cell found in a set column, paste that content into the main file in a desitnation found via the first empty cell found in a set column. Problem is the script seems to revert to a destination reference in the file to copy instead of the main file. Below is the code, help is greatly appreciated.
Also, I found that I had to be repetitive in using “tell app MSExcel…” other wise the script would revert into telling “Finder”.
set AppleScript's text item delimiters to ""
set i to {}
tell application "Finder"
activate
set thePath to alias "Work:Users:mcreynos:Desktop:Jan Outlines:PrintTracking-Tests:PT Main_Read-Only.xls"
set theMMFolders to alias "Work:Users:mcreynos:Desktop:Jan Outlines:PrintTracking-Tests:PT-MM Files:PT-20000s.xls"
set theTargetFile to thePath
set theMMFiles to theMMFolders
tell application "Microsoft Excel"
open theTargetFile
repeat with i from 1 to count of theMMFiles
open file i of theMMFiles
tell file i of theMMFiles
tell application "Microsoft Excel" to set theMMWorksheet to the worksheet "Sheet1"
tell application "Microsoft Excel" to set MMSearchValue to column 5 of theMMWorksheet
tell application "Microsoft Excel" to set MMAfterCell to cell "E1" in theMMWorksheet
tell application "Microsoft Excel" to set TheMMSearch to find MMSearchValue what "" after MMAfterCell look in values look at whole search direction search next
tell application "Microsoft Excel" to set firstMMEmptyCell to get address TheMMSearch
set AppleScript's text item delimiters to "$"
tell application "Microsoft Excel" to set theMMRow to text item 3 of firstMMEmptyCell
tell application "Microsoft Excel" to set theMMContent to (value of range {"$A$2:$F$" & theMMRow} as string)
end tell
tell theTargetFile
tell application "Microsoft Excel" to set theTargetWorksheet to the worksheet "Sheet1" of theTargetFile
tell application "Microsoft Excel" to set TargetSearchValue to column 1 of theTargetWorksheet of theTargetFile
tell application "Microsoft Excel" to set TargetAfterCell to cell "A1" in theTargetWorksheet
tell application "Microsoft Excel" to set FoundTargetRow to find TargetSearchValue what "" after TargetAfterCell look in values look at whole search direction search next
tell application "Microsoft Excel" to set firstTargetEmptyCell to get address FoundTargetRow of theTargetFile
tell application "Microsoft Excel" to set value of firstTargetEmptyCell of theTargetFile to theMMContent
end tell
end repeat
end tell
end tell
set AppleScript's text item delimiters to ""
I have not been successful scripting Excel with multiple open documents. I do not know if it is me or Excel, but I am comfortable blaming Excel…
Anyway, I believe that your best approach is to create a global variable, set it to an empty list, and repeat through each document one at a time, holding all of the data in the global variable. Once all the data is gathered, open the target document and put it in the appropriate places.
According to Excel’s scripting reference you need to activate the workbook and worksheet that you are working on, you can’t just refer to it by index number so you need to do something like this:
set FileOne to choose file
set FileTwo to choose file
tell application "Microsoft Excel"
activate
open FileOne
set FileRefOne to name of active workbook
log FileRefOne
open FileTwo
set FileRefTwo to name of active workbook
log FileRefTwo
activate object worksheet 1 of workbook FileRefOne
set ImportValue to string value of range "A7"
activate object worksheet 1 of workbook FileRefTwo
set value of range "F4" to ImportValue
end tell
Which seems to work for me copying one cell from one document to another cell of the second.
A few other notes, for setting the variables to aliases like you are doing you do not need the tell finder block. Also the multiple tells to Excel are redundant and could be causing some problems. Just activate the appropriate worksheet in the appropriate workbook that you are targeting with your commands and it should switch between the documents and worksheets for you and manipulate them as expected.
I finally got it to work…I found that Excel works best without any tell blocks and just “activate” the file you’re working on instead. Also I found some characters can make it crash, when I’d copy a cell with “$” in it the app. would crash, probably because it’s used to signify ranges. Here’s my final script if anyone is curious.
set i to 0
set the_Folders to "file path to folder containing excel files"
set List_of_Files to list folder the_Folders without invisibles
set Target_File to "file path to main excel file to paste others into"
tell application "Microsoft Excel"
activate
open Target_File
set ColumnAStart to 2 --starts right after main heading, variable set to move down rows when pasting
set first_content_row to 2
repeat with i from 1 to count of List_of_Files
set Copied_File to the_Folders & item i of List_of_Files
open Copied_File
activate Copied_File
set Column_to_Search to range "C:C" --search this column
set cell_to_start_search_after to range "C1" --begin search after this cell
set empty_cell_search to find Column_to_Search what "" after cell_to_start_search_after look in values look at whole search direction search next --look for empty cell in given range
set empty_cell to get address empty_cell_search --set address of empty cell to string
set AppleScript's text item delimiters to "$" --look for column and row references
set last_column_copied to text item 3 of empty_cell --row reference
set AppleScript's text item delimiters to ""
set content_copied to value of range ({"$A$2:$GB$" & (last_column_copied - 1)} as string) --copies range of cells using row variable and set column
close active workbook without saving
activate Target_File
---set first_content_row to (first_content_row + last_column_copied)
set Target_Column_searched to range "C:C"
set Target_cell_to_search_after to range "C1"
set Target_empty_cell_search to find Target_Column_searched what "" after Target_cell_to_search_after look in values look at whole search direction search next
set firstMainEmptyCell to get address Target_empty_cell_search
set AppleScript's text item delimiters to "$" --look for column and row references
set theEmptyMainRow to text item 3 of firstMainEmptyCell
set first_content_row to (theEmptyMainRow + (last_column_copied - 3))
set AppleScript's text item delimiters to ""
set Area_for_copied_content to range ({"$A$" & theEmptyMainRow & ":$GB$" & first_content_row} as string)
copy contents of content_copied to value of Area_for_copied_content
end repeat
end tell