1

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?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Sancarn
  • 2,575
  • 20
  • 45
  • **Please [edit] your question to add the code showing the loop that you're using**, as there are a number of ways you could be doing this, so the correct answer could vary. The error's probably occurring if you try to paste at the same split-second that the clipboard is being populated. Are you pausing at all between each attempt? Are you using `DoEvents`? – ashleedawg Aug 08 '18 at 23:22
  • It may also be helpful to know a little background about what you're doing. Often with something like this there's a better (less problem-prone) way to get the job done by looking at it a different way. For example, where is this clipboard data coming from? Perhaps it would be better for the source application to proactively trigger your task a from that end (by simply calling a procedure), instead of retroactively "watching" for it from this end, like a custom event. – ashleedawg Aug 08 '18 at 23:27
  • @ashleedawg I really don't think the code will help to be honest. It will just confuse matters more. I am converting linked images to embedded images. So it's already using a 'hack' that copy/paste embeds an image rather than links it. Sadly, using copy and paste like this is really the only way of doing this also, unless you want to snoop around in the decompressed Excel xml file, which I have considered. – Sancarn Aug 08 '18 at 23:35
  • @ashleedawg on the off chance it will help, I've added more detail to the question. – Sancarn Aug 08 '18 at 23:38
  • Possible duplicate of [Excel VBA Macro: Check content (of clipboard?) before pasting](https://stackoverflow.com/questions/1108947/excel-vba-macro-check-content-of-clipboard-before-pasting) – Comintern Aug 08 '18 at 23:46
  • @Comintern I'm afraid the marked question is named incorrectly. The author was asking for something completely different. – Sancarn Aug 08 '18 at 23:49
  • Read [this answer](https://stackoverflow.com/a/13914224/4088852). It shows how to do exactly what you're trying to do. – Comintern Aug 08 '18 at 23:54
  • @Comintern ... No? It doesn't? This is pasting text data to the page, for one, not image data. This doesn't look like it's "waiting" for data to be ready to me...? Nothing about this answer is waiting for data to be ready or even getting the data for that matter... – Sancarn Aug 08 '18 at 23:57
  • 1
    Read the part that checks to see if a clipboard format is available. It doesn't matter what *kind* of data that answer is checking - it shows how to check the clipboard for data. – Comintern Aug 09 '18 at 00:01
  • @Comintern Mhmm. Right, I see what you mean. Didn't see it that way at first. Will give it a go and see if I can get that working. – Sancarn Aug 09 '18 at 00:06

3 Answers3

0

I think you probably just need to add a pause to give the clipboard a moment to populate the clipboard before you try to access it.

I use a pause procedure like: 

Sub Pause(sec as Single) 
    Dim startTime as Single 
    StartTime=Timer 
    Do 
        DoEvents
    Loop While StartTime + sec > Timer 
End Sub

I'd probably start with a quarter or half second pause (ie., Pause (0.25)) and then loop until the clipboard's ready.

A half second should be more than enough time, although ideally you'd be triggering your procedure from the other end after pasting, proactively instead of re actively.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Personally I'd like to avoid using a pause. If it is 'populate time' issue then pausing for any fixed amount of time doesn't work for files larger than a certain size. Also it may be processor dependant. – Sancarn Aug 09 '18 at 07:56
0

I was searching something similar (proper syntax on API calls for the clipboard), and came across this thread. It may be worth noting that when you send data to the clipboard, it indeed takes time.

The "Do events while busy" I used to use came to mind when using my old IE.navigate commands.

Anyway, what do you think of this.

SendKeys ("^c") 'Psudeocode to copy

JumpPoint:

RandomVar = Clipboard.GetData

DoEvents 'Force this data into a random address somwhere while Clipboard loads

If ClipBoard.GetData <> RandomVar Goto JumpPoint

[Do the Magic here]

See that? If RandomVar = the current state of the ClipBoard, and Do Events makes it so that RandomVar is equal to SOMETHING referencable, then as ClipBoard continues to aggregate data, it will differ from RandomVar. Thus the Goto will kick back a tick, essentially "Do Events While Busy".

That's just my thought process using the tools you already have available without seeing the entire code or intention.

Yes, my coding methods are blasphemy. I don't care. Good luck, and my the dark side be ever with you.

user19088256
  • 1
  • 1
    This is a pretty old question, so my understanding has evolved greatly since. I definitely wouldn't suggest using `SendKeys()` for this, given `SendKeys` is asynchronous. The better option would be to use: [`stdClipboard`](https://github.com/sancarn/stdVBA/blob/master/src/stdClipboard.cls) and call either the `Wait` and/or `IsFormatAvailable` methods. – Sancarn May 10 '22 at 21:07
0

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.

Sancarn
  • 2,575
  • 20
  • 45