Tuesday, December 12, 2017

#1 2017-09-15 09:05:59 am

JeremyHoward
Member
Registered: 2016-09-28
Posts: 3

Placing Images In Excel 2016

Hello all, 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:

Applescript:


(* Sets the path to the images folder *)
tell application "Finder"
   set basePath to (((parent of (path to me)) as string as alias) & "TempFiles:") as string as alias
   set tempPath to (((parent of (path to me)) as string as alias) & "TempImageHold:") as string as alias
   set nameList to name of (every folder of basePath) as list
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

Model: iMac (Retina 5K, 27-inch, 2017)
AppleScript: AppleScript 2.5
Browser: Firefox 55.0
Operating System: Mac OS X (10.12.6 beta 6)


Filed under: applescript, image, excel, Insert

Offline

 

#2 2017-09-15 10:59:30 am

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11482
Website

Re: Placing Images In Excel 2016

Not related to the issue but your coercion orgy in the first lines is hilarious.

You can reduce the coercions dramatically by using System Events, and nameList is always a list, the coercion is redundant.

Applescript:

tell application "System Events"
   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


regards

Stefan

Offline

 

#3 2017-09-15 11:52:39 am

JeremyHoward
Member
Registered: 2016-09-28
Posts: 3

Re: Placing Images In Excel 2016

Yeah, I did get a little crazy up there... lol

I am 100% self taught and every once in a while things like that slip through from some past script that I got to work and called Good enough smile

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)