CopyPicture method of range class failed - sometimes

Question

I have a VBA code which I am using to copy ranges as a picture and paste them into a chart. It does this so I can save it into a picture. This code has like a 70% success rate, and when it doesn't work, it gives out the error "CopyPicture method of range class failed". I don't understand why it can sometimes work and sometimes doesn't given that it is taking the same inputs.

Can anyone help?

Public Sub ExportRange(workbookPath As String, sheetName As String, rangeString As String, savepath As String)

    Set tempWorkBook = Workbooks.Open(workbookPath)

    Dim selectRange As range
    Set selectRange = Worksheets(sheetName).range(rangeString)
    Dim numRows As Long
    numRows = selectRange.Rows.Count
    Dim numCols As Long
    numCols = selectRange.Columns.Count

    ' Transfer selection to a new sheet and autofit the columns
    selectRange.Copy
    Dim tempSheet As Worksheet
    Set tempSheet = Sheets.Add
    tempSheet.range("A1").PasteSpecial xlPasteAll

    ActiveSheet.UsedRange.Columns.AutoFit
    Set selectRange = ActiveSheet.UsedRange
    selectRange.Select
    selectRange.CopyPicture xlScreen, xlPicture

    Dim tempSheet2 As Worksheet
    Set tempSheet2 = Sheets.Add
    Dim oChtobj As Excel.ChartObject
    Set oChtobj = tempSheet2.ChartObjects.Add( _
        selectRange.Left, selectRange.Top, selectRange.Width, selectRange.Height)

    Dim oCht As Excel.Chart
    Set oCht = oChtobj.Chart
    oCht.Paste
    oCht.Export filename:=savepath
    oChtobj.Delete

    Application.DisplayAlerts = False
    tempSheet.Delete
    tempSheet2.Delete
    tempWorkBook.Close
    Application.DisplayAlerts = True

End Sub

Does it fail for the same image / worksheet, etc? or in different cases? As in if you run a loop executing this 100 times, does it run 0 times for some cases and 100 times for others or does it run some number in between and then stop with the error? — hnk, Jul 14 '14 at 15:37
It fails for the same image and worksheet. I run this Sub in a loop with 6 images, and when it fails, it can fail on any one of the images, not necessarily always the same one. — Horace, Jul 14 '14 at 15:44
Try enclosing your main code logic within `Application.EnableEvents = False` and `Application.EnableEvents = True` — hnk, Jul 14 '14 at 15:47
Just tried it, same problem. Sometimes it work sometimes not. And when it doesn't it doesn't stop on the same part of the loop of 6 images every time. — Horace, Jul 14 '14 at 15:55
I replaced the workbook that I copy the ranges from with a blank sheet and it produces the error as well, perhaps even more often so. Could it be that the code is running so fast that some parts of it is not waiting for the previous code to finish? — Horace, Jul 14 '14 at 15:59
ohhh... in that case try running the code in debug mode. put enough breakpoints, and slowly cycle through it. See if it fails now. if it doesn't then you've solved the problem — hnk, Jul 14 '14 at 16:01
insert Application.DoEvents or simply `DoEvents` where you would like a bit of slowdown and see what happens. — hnk, Jul 14 '14 at 16:03
Had the same problem some times that I (king of?) solved by catching the error and resuming the execution on the same line. — Vincent G, Jul 20 '16 at 15:51
Had same problem when invoking CopyAsPicture through win32com (from python). Ugly workaround: 10 retries.. — glexey, Sep 22 '16 at 22:00
Anybody looking for solution, use this before paste: `.ChartArea.Parent.Select` https://stackoverflow.com/a/53844969/427969 — user427969, Jun 07 '21 at 04:30
@VincentG, I'm a bit of a novice - how do you resume execution on the same line? — codeEnthusiast, Feb 28 '23 at 01:10
From inside an error handler, `Resume` will resume execution from the line that generated the error, `Resume Next` will resume execution from the next line. `Resume someplace` will resume execution from the `someplace:` label. — Vincent G, Feb 28 '23 at 16:20

Patrick Lepelletier · Answer 1 · 2021-03-19T13:59:19.707

4

i have found a way to force excel to wait until the clipboard has a picture in it, because sometimes it's too fast:

Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 

'just after copypicture, add this: (in my case i added it inside pastepicture, or i'd have too much coding )
Dim T#
  Do
      Waiting (2)
Loop Until IsClipboardFormatAvailable(2) Or Timer - T > 0.3

Sub Waiting(ByVal Mili_Seconds&)
Sleep Mili_Seconds
End Sub

edited Mar 19 '21 at 13:59

answered Dec 15 '17 at 18:37

Patrick Lepelletier

1,596
2
17
24

Thanks ! in my case "applicaiton.displayalert = false" was the problem ! – Elloco Feb 12 '18 at 08:01
This does not work always! Especially in Excel 2016. – sifar Apr 20 '20 at 07:46
edited the answer ans simplified it. This final solution works ALWAYS – Patrick Lepelletier Mar 19 '21 at 13:59

Chema Vascuence · Answer 2 · 2016-11-14T21:02:57.200

3

I was struggling with the very same issue than you and I think is nothing to do with our VBA code or lack of programming skills. The error it's too random.

Moreover, if after getting the error message I clicked DEBUG and pressed F8 to continue executing the code step by step, then I was able to skip the error. After the problematic line I pressed F5 to continue in normal execute mode.

Of course, the above is not a solution but reveals nothing wrong with my coding.

Well, I did this and it worked for me:

before this sentence,

rgToPic.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

I added this one:

rgToPic.Copy  'just for nothing

and I never have had the error in CopyPicture method again.

Looking for this issue in other places I found out some users were able to skip the error by introducing this sentence before the CopyPicture method:

    application.CutCopyMode=false

edited Nov 14 '16 at 21:02

answered Nov 14 '16 at 18:38

Chema Vascuence

59
3

1

That was the solution for me.Just debugging the code worked.I had to hit F8 all the way! – Sotiris Zegiannis Sep 29 '17 at 16:14
'Application.CutCopyMode=false' worked well for me. Thanks. – JLuc01 Oct 14 '20 at 22:07
Thanks. My own solution was to add a 800 ms `WAIT` time before the `.CopyPicture` command (600 ms didn't work). Then, I found this page. I have tried every method on this page to no avail until this one - adding a `.Copy` command before `.CopyPicture`. I still need a wait time but it's now 50 ms. – joehua Oct 28 '20 at 06:55
That was also the solution for me. After spending almost 1 day, finally I found this. Thanks. – Frank Myat Thu Nov 25 '21 at 09:30

score 2 · Answer 3 · edited Jun 20 '20 at 09:12

2

The only thing that worked for me was to add a delay BEFORE the CopyPicture method. We are tweaking it shorter as I type this, but I know a 50 ms delay was working fine:

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'Set Range you want to capture

Dim rgExp As Range: Set rgExp = Range("B2:D6")

Sleep (50) ' Pause in milliseconds to prevent runtime error on CopyPicture, your system may be able to use shorter sleep, or may need longer...

' Copy range as picture onto Clipboard

rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

edited Jun 20 '20 at 09:12

Community

1
1

answered May 06 '19 at 13:18

Brett G

21
1

Indeed. This is a timing runtime error. – Brethlosze Aug 29 '22 at 00:13

score 1 · Answer 4 · answered Feb 26 '17 at 06:12

Although this is an old post, maybe this will help someone. I was struggling with similar problem for a long time. CopyPicture failed (on some computers more often than others, but hard to replicate on my laptop) when I was copying the range that contained an embedded PNG picture. It only failed in Application.Visible=0 mode, Application.Visible=1 worked fine (for my application it is mandatory to run Excel in invisible mode). Finally I found that I can reproduce the problem 100% of the times when run on a VM with 1 CPU. The following solution is weird, but seems to be solving my problem completely.

Embedded PNG is a Shape in Excel API terms. I just needed to cycle through the shapes (not even doing anything) before calling CopyPicture:

# 'rng' is a range that I want CopyPicture on 
for shape in rng.Shapes: pass
rng.CopyPicture(xlScreen, xlBitmap)

My finding is somewhat similar to this solution, where CopyPicture was failing on a range with charts. In their case, activating workbook and range itself helped.

Hypothesizing, it seems plausible that on a slow or heavily loaded computer Excel does "lazy processing" of the complex objects on a page, i.e. not rendering them until object is accessed in some way. One way to force rendering seems to run in Visible=1 mode. Another way is to cycle through the objects. If this is the case, then it is a bug of Excel's CopyPicture implementation where it doesn't force complex objects to render before trying to copy. When copy method finds out rendering for the target range is not ready, it simply throws an error instead of forcing the range to render. Well, at least that's my theory.

score 1 · Answer 5 · answered Feb 25 '20 at 13:48

1

My work around for this was to throw it in an error catching while loop and keep retrying it until it was able to fully copy the range without an error message. Works like a charm now.

answered Feb 25 '20 at 13:48

Austin

11
1

score 1 · Answer 6 · answered Mar 11 '21 at 09:03

1

What worked for me, was clearing the clipboard before I copied the picture with Application.CutCopyMode = False

Best practice I imagine is to paste what you need, and directly afterwards clear the clipboard.

answered Mar 11 '21 at 09:03

MrNago

11
1

score 0 · Answer 7 · answered Jul 20 '16 at 15:45

0

For me I had similar problem and I could solve it by changing between xlScreen and xlPrinter in selectRange.CopyPicture

I hope this helps

answered Jul 20 '16 at 15:45

IdontCareAboutReputationPoints

1,584
21
26

score 0 · Answer 8 · answered Jul 12 '19 at 05:43

The CopyPicture method sends the result to clipboard. But due to security reason, Win10 forbids access to clipboard while screen is locked. Therefore if you run the macro while locking screen, the CopyPicture method will fail with error code 1004.
The same error happens with Worksheet.Pictures.Paste.

On the other hand, simple Copy and PasteSpecial won't pop error. When the clipboard is not accessible, the content won't be copied to clipboard but VBA won't complain about it.

Unfortunately, PasteSpecial doesn't have the option to paste as picture.
The only simple workaround is leaving your computer unlocked while running the macro.

Your information doesn't seem to account for the following from the original poster: "This code has like a 70% success rate". — Andrew Jens, Dec 02 '20 at 19:43

score -1 · Answer 9 · answered Mar 22 '23 at 15:01

Since all of the solutions above still didn't fix the issue for me I kept searching and finally ChatGPT helped me build the following code after asking it to use TRY / CATCH in vba:

..
                Dim retryCount As Integer
                retryCount = 0
retry_CopyPicture:
                On Error GoTo ErrorHandler
                oRng.CopyPicture xlPrinter, xlPicture
                GoTo ContinueExecution
            
ErrorHandler:
                retryCount = retryCount + 1
                If retryCount <= 10 Then
                    Application.Wait (Now + TimeValue("0:00:01"))
                    Resume retry_CopyPicture
                Else
                    MsgBox "Error while trying to copy the picture 10 times. Macro Aborts."
                    Exit Sub
                End If
            
ContinueExecution:
...

Since using this approach I count every once in a while a retryCount of 1 or 2. The code now works flawlessly.

You just captured the possible issue not solve them. Just rember to figure what is going wrong should be the first principle. — Horsing, Mar 31 '23 at 06:42

score -4 · Answer 10 · edited Jul 31 '18 at 16:12

I found a easy way to fix this issue with which I was struggling for a few months. I know this is a "BAD CODE" but it helped and worked perfect for me. In my case details were getting copied but the debug error window was populating. Hence I just skipped the debug window and my life became easier.

Fix is just add below code in front of the "copy" code in your VBA. This will sure fix this error.

On Error Resume Next

CopyPicture method of range class failed - sometimes

10 Answers10

Linked