I'm building a process to convert linked images into embedded images in Excel:
for ws in wb.sheets
count = ws.shapes.count
for 1 to count
'Get first shape from collection
set shp = ws.shapes(1)
'Store shape's position/size
'...
'Break link if it exists
shp.CopyPicture
ws.Paste
shp.delete
set newShp = ws.shapes(count)
'Assign newShp, shp's old position/size
'...
next shp
next ws
Sometimes the code will error on line the 2nd line of:
shp.CopyPicture
ws.Paste
with the error "Unable to execute method paste...". This occurs also when I space out the copy and paste methods with DoEvents
like so:
shp.CopyPicture
DoEvents
ws.Paste
DoEvents
However after clicking debug, and waiting a second or two, and pressing play again everything continues working like a charm.
I suspect Excel isn't waiting long enough for the CopyPicture
method, to fully occupy the clipboard. Assuming this is the case, can I monitor the clipboard somehow and wait till the clipboard data is full?
It's been a while since I asked this question. Since then my knowledge has grew in this arena. In the future I'll use my stdClipboard
class's xlShapeAsPicture
method as follows:
for ws in wb.sheets
count = ws.shapes.count
for 1 to count
'Get first shape from collection
set shp = ws.shapes(1)
'Store shape's position/size
'...
'Break link if it exists
set stdClipboard.xlShapeAsPicture = shp '<<<---
'Paste
ws.Paste
'...
next shp
next ws
The key to this solution is making calls to poll IsFormatAvailable()
after shp.CopyPicture()
is called to wait until the image format is available, and only then exit the function and continue runtime.