Wait for clipboard to contain data

Question

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?

Answer 1

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.