AppleScript to Insert Images Into Excel 2016

[JeremyHoward]

Hello all, I am a long time reader and a first time poster and I hoping that someone can help me resolve an issue with a script that is vital to my regular workflow. This script gathers data from "Image Folders" and enters that data into a spreadsheet alongside the image itself.

I have been using this script on a mac that runs Excel 2011 for well over a year now and it has been working flawlessly. The problem that I am having occurs when I attempt to run this script on one of the newer Macs in the office, a mac that is running "Excel For Mac" as part of the Office 365 suite.

Oddly enough, this script works as expected through 2 cycles of the main repeat block and then, upon trying to insert the third image, Excel errors out and the script stops.

Any help is much appreciated, thanks all!

The Script:

 (* Sets the path to the images folder *)
tell application "Finder"
   set parentFolder to path of container of (path to me)
   set basePath to alias (parentFolder & "TempFiles:")
   set tempPath to alias (parentFolder & "TempImageHold:")
   set nameList to name of every folder of basePath
end tell

----------------------------------------------------------------------------------------------------------

set cellCount to 6

repeat with i in nameList
----------------------------------------------------------------------------------------------------------
(* Name of image based on enclosing folder *)
set imageName to i as string
(* Path to enclosing folder *)
set currentFolder to (basePath & imageName) as string as alias

--Finds the JPEG of the image - the reg number and date
tell application "Finder"
(* image file *)
set theImage to (every file of currentFolder whose name contains imageName) as string
(* Data is in the names of these folders *)
set regNumName to (name of (every folder of currentFolder whose name contains "Registration Number")) as string
set regDateName to (name of (every folder of currentFolder whose name contains "Registration Date")) as string
set cDateName to (name of (every folder of currentFolder whose name contains "Creation Date")) as string
set pDateName to (name of (every folder of currentFolder whose name contains "Publication Date")) as string
end tell

(* Run Photoshop action on image file - this action saves the image in folder tempPath *)
tell application "Adobe Photoshop CC 2017"
open file theImage
delay 0.5
do action "Excel Image Info" from "Automation Actions"
end tell

(* Refines names of "Data Folders" *)
set rNumList to splitText(regNumName, " - ")
set rNum to (item 2 of rNumList) as string
set rDateList to splitText(regDateName, " - ")
set rDate to (item 2 of rDateList) as string
set cDateList to splitText(cDateName, " - ")
set cDate to (item 2 of cDateList) as string
set pDateList to splitText(pDateName, " - ")
set pDate to (item 2 of pDateList) as string

(* Makes sure the image exists in tempPath before proceeding *)
repeat
try
tell application "Finder" to set theImage to (every file of folder tempPath whose name contains imageName) as string as alias
exit repeat
end try
end repeat

(* Enter Data and Place Image in Excel *)
tell application "Microsoft Excel"
set theSheet to active sheet of active workbook
tell theSheet
set imageCell to cell ("B" & cellCount)
set nameCell to cell ("D" & cellCount)
set regNumCell to cell ("D" & cellCount + 1)
set regDateCell to cell ("D" & cellCount + 2)
set cDateCell to cell ("D" & cellCount + 3)
set pDateCell to cell ("D" & cellCount + 4)

set value of nameCell to imageName
set value of regNumCell to rNum
set value of regDateCell to rDate
set value of cDateCell to cDate
set value of pDateCell to pDate

set imageHeight to 90
set imageWidth to 153

set theLeft to (left position of imageCell)
set theTop to (top of imageCell)

set newPic to make new picture at beginning with properties {file name: (theImage as text), height:imageHeight, width:imageWidth, top:theTop, left position:theLeft, placement: placement move}
end tell --sheet
end tell --excel

set cellCount to cellCount + 8
end repeat


on splitText(theString, theDelimiter)
-- save delimiters to restore old settings
set oldDelimiters to AppleScript's text item delimiters
-- set delimiters to delimiter to be used
set AppleScript's text item delimiters to theDelimiter
-- create the array
set theArray to every text item of theString
-- restore the old setting
set AppleScript's text item delimiters to oldDelimiters
-- return the result
return theArray
end splitText