Hello Scripters, had a real head scratcher here that I was hoping someone could help me with.
I’m trying to script picture replacements in an Excel 2008 spreadsheet.The images ogitinal exist in the spreadsheet, and the replacements are sitting in a folder next to the document.
I cannot figure out how to do either a Change Picture or Insert Picture/From File within Applescript.
I did come across the ability to throw up an Insert Picture/From File dialog:
tell application “Microsoft Excel”
show (get dialog dialog insert picture)
end tell
… but, I don’t want the dialog, I want the script to avoid the dialog altogether. Oddly, there doesn’t seem to be a “dialog insert picture” constant… or a Change Picture menu item for that matter (the “Change Picture” command appears to be available only from the Contextual Menu).
Even if I did choose to do it via UI Scripting, there doesn’'t even a seem to be a way to get the result of the show (get dialog dialog insert picture) command.
One has to wonder just what drugs Microsoft was on when they missed this in their otherwise very extensive Applescript dictionary.
Anyway, has anyone gotten around this, or perhaps someone could pass along their strategy for updating images? I cannot use OLE image linking, since the document I’m working on has been cloned from a different project, and any OLE links would be associated with the original client, not the intended one.
tell application "Microsoft Excel"
make new picture at the beginning of worksheet 1 of workbook "Workbook1.xls" with properties {file name:"Macintosh HD:Users:merickson:Pictures:Photo Booth:Photo 1.jpg"}
end tell
thanks, Mike, that goes a long way to solving this problem. one thing I couldn’t figure out (after googling my brains out) was how to change the reference:
at “the beginning of” into a specific cell reference, e.g.: cell “H2”:
tell application “Microsoft Excel”
make new picture at cell "H2" of worksheet 1 of active workbook with properties {file name:"Edison:Users:cameron:Data:Clients:SL411:ict:status reports:images:logo.jpg"}
end tell
When I run this, I get the error:
Microsoft Excel got an error: Can’t make class picture.
I didn’t seem to have any control over the formatting of the image that did appear when I used “at the beginning of”, although I’m sure I can manipulate that. Still, the location is critical.
tell application "Microsoft Excel"
set newPic to make new picture at the beginning of worksheet 1 of workbook "Workbook1" with properties {file name:"Macintosh HD:Users:merickson:Pictures:Photo Booth:Photo 1.jpg"}
set left position of newPic to left position of range "H2"
set top of newPic to top of range "H2"
end tell
I thought that might be what I had to do (create and move), you’re defnitely pointing me in a good direction.
however, as the image comes in as a 25x25 pixel reduction of itself, I need to resize the image… but I don’t know how large the image is supposed to be. that info doesn’t seem to be in the Excel dictionary. “scale height” and “scale width” look tempting, but those are in the Drawing suite, they don’t seem to have an affect on our poor little logo.
tell application “Microsoft Excel”
set theDoc to worksheet 1 of active workbook
set oldLogo to shape “Logo” of theDoc
set shapes default properties oldLogo
set newLogo to make new picture at the beginning of theDoc with properties {file name:"Edison:Users:cameron:Data:Clients:SL411:ict:status reports:images:logo.jpg"}
set left position of newLogo to left position of range "H2"
set top of newLogo to top of range "H2"
scale width newLogo factor 1 with relative to original size
scale height newLogo factor 1 with relative to original size
end tell
error: Microsoft Excel got an error: picture 9 of worksheet 1 of active workbook doesn’t understand the scale width message.
thanks so much for your guidance on this one, I’m an intermediate Applescript programmer, which means I know enough to get myself lost pretty well. I do find Applescripting pretty rewarding though, thanks to helpful people like you.
Looking at the AppleScript dictionary for Excel, under Drawing, picture, I saw “height” and “width”. I added 2 lines, after placing the image, and it sized the image:
set image_file to (choose file with prompt "Choose an image file") as text
tell application "Microsoft Excel"
set newPic to make new picture at the beginning of worksheet 1 of workbook "Workbook1" with properties {file name:image_file}
set left position of newPic to left position of range "H2"
set top of newPic to top of range "H2"
set height of newPic to 128
set width of newPic to 128
end tell
This simplified version (of the Excel command) also works:
set image_file to (choose file with prompt "Choose an image file") as text
tell application "Image Events"
launch
set this_image to open alias image_file
set image_dimen to dimensions of this_image
set w to item 1 of image_dimen
set h to item 2 of image_dimen
close this_image
end tell
tell application "Microsoft Excel"
set wb_name to name of window 1
set left_pos to left position of cell "B2"
set top_pos to top of cell "B2"
set newPic to make new picture at the beginning of worksheet 1 of workbook wb_name with properties {file name:image_file, left position:left_pos, top:top_pos, height:h, width:w}
end tell
for future others, this is the generalized routine I came up with to replace a picture/shape/image in the current Excel spreadsheet. It grabs the previous location and size of the old picture (baed on the name of the image), deletes it, then places the new picture at the right location and size (or [poorly] assumes a default location and size if an existing picture of that name does not exist):
tell application “Microsoft Excel”
set theSheet to active sheet of active workbook
set imageName to "Logo"
set newImagePath to "Edison:Users:cameron:Data:Clients:SL411:ict:status reports:images:logo.jpg"
-- insert the picture at a specific location and size:
if (exists shape imageName of theSheet) then
set oldImage to shape "Logo" of theSheet
set topPos to top of oldImage
set leftPos to left position of oldImage
set imageHeight to height of oldImage
set imageWidth to width of oldImage
delete oldImage
else
set topPos to top of cell "H2"
set leftPos to left position of cell "H2"
set imageHeight to 63
set imageWidth to 207
end if
set newImage to make new picture at the beginning of theSheet with properties {name:imageName, file name:newImagePath, left position:leftPos, top:topPos, height:imageHeight, width:imageWidth, placement:placement free floating}
end tell
this script assumes that old and new images will be identical in size; I couldn’t figure out how to grab the actual size of a new picture (or for that matter, the actual size [not the currently scaled size] of an existing picture).
set fileName to "Macintosh HD:Users:merickson:Pictures:Photo Booth:Photo 1.jpg"
tell application "Image Events"
set aFile to open file fileName
set {origWidth, origHeight} to dimensions of aFile
close aFile
end tell
tell application "Microsoft Excel"
set myPic to make new picture at the beginning of worksheet 1 of workbook "Workbook1" with properties {file name:fileName}
set lock aspect ratio of myPic to false
set height of myPic to origHeight
set width of myPic to origWidth
end tell
weird, Image Events doesn’t seem to be helping me out much… the open command actually opens the file via the Finder into Photoshop (and gives me no file reference).
tell application “Image Events”
launch
set aFile to open newLogoFile
set {origWidth, origHeight} to dimensions of aFile
close aFile
end tell
– error: The variable aFile is not defined.
I’m looking at the Image Events dictionary, and it looks kosher. Am I missing the targetting of that command? I figured since I was in a tell block that Image Events would be the target of the open command, not the Finder.
In Office 2011, VBA is back so you can use it to insert a picture since there is the problem of size (25x25).
For example, you create the function in your Personal Macro Workbook :
Sub insert_picture(pWorkbook As String, pWorksheet As String, pPath As String)
If pPath <> "False" Then
Workbooks(pWorkbook).Worksheets(pWorksheet).Pictures.Insert(pPath).Select
Else
Exit Sub
End If
End Sub
and in Applescript you use the VBA script :