CopyPicture method of Range class failed

[gmerrick]

I'm using Excel 2010 (1x.0.???), 2013 (15.0.4797.1003) and Excel 2016 (16.0.6568.2025) on three independent PCs.

For ease in reproducing the issue, I start with a blank worksheet, and enter "1" into cell A1 of Sheet1. I then create this macro:

Sub Test()
For x = 1 To 100
Range("A1:A1").CopyPicture
Next x
End Sub

When I run macro Test in Excel 2010, it never halts with an error... x always reaches 100.

When I run macro Test in Excel 2013 or 2016, it halts with "CopyPicture method of Range class failed". x has a random value, but somewhere between 5 and 10 is typical. A breakpoint at Next x doesn't help, so I rule out a timing issue.

BUT THIS ONLY STARTED TO OCCUR in Excel 2013 and 2016 in the past week. The real macro is much more complex, but the trivial macro leads me to think some auto update of Excel has suddenly broken CopyPicture!

Our company relies on this method many times in a day. Any ideas would be greatly appreciated.

[Paul_Hossler]

Using Win10, 64 bit, Excel 16.0.6568.2025 it seems to work ok with a new empty workbook

Capture.JPG


You might try the Code Cleaner to 'refresh' your macros. It often fixes that sort of problem

http://www.appspro.com/Utilities/CodeCleaner.htm

[gmerrick]

I changed my macro to read precisely like yours. My immediate window shows after over 3 tries it ran 3, 8 and 5 iterations.

It's helpful to know that you're running the same build without seeing the issue. I'll try it on some additional desktops here. I can also try repairing my installation of Excel, but with it occurring on several desktops I'm not hopeful this will work.

Thanks for the tip on CodeCleaner, but if I can't get this trivial macro to execute properly the problem must lie elsewhere.

[Paul_Hossler]

1. Good luck tracking the problem down

2. CodeCleaner actually works on all the modules in the workbook, not just a simple macro

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

3. Did you try opening a brand new WB, and just using the simple macro all by itself to see it the issue still persist?

[gmerrick]

This is a new WB. I'm at a loss to understand why the problem only started to appear a few weeks ago, first in Excel 2013 and then Excel 2016. My theory was a recent update to Excel broke something, but then you tested on the same build and it's working.

[Paul_Hossler]

This is a new WB. I'm at a loss to understand why the problem only started to appear a few weeks ago, first in Excel 2013 and then Excel 2016. My theory was a recent update to Excel broke something, but then you tested on the same build and it's working.

Try to 'Repair' Office and see is the only idea I have left

[Paul_Hossler]

Possibly change the default printer to another one

Printer drivers can be tricky also

[gmerrick]

Just for fun I added a delay in the loop:

Sub Test()
    Dim x As Long
    For x = 1 To 100
        Application.Wait (Now + TimeValue("0:00:01"))
        Debug.Print x
        Range("A1:A1").CopyPicture
    Next x
End Sub

Now it works fine, but takes 100 seconds to complete. :-( Does this provide any clue as to what's happening?

[Cucho]

Just for fun I added a delay in the loop:

Sub Test()
Dim x As Long
For x = 1 To 100
Application.Wait (Now + TimeValue("0:00:01"))
Debug.Print x
Range("A1:A1").CopyPicture
Next x
End Sub

Now it works fine, but takes 100 seconds to complete. :-( Does this provide any clue as to what's happening?

I was struggling with the very same issue than you.

Well, I did this and it worked for me:

I added this sentence:

rgToPic.Copy

immediately before of this sentence:

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

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

[gmerrick]

I was struggling with the very same issue than you.

Well, I did this and it worked for me:

I added this sentence:

rgToPic.Copy

immediately before of this sentence:

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

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

Your fix worked for me! We've been putting up with this irritation for 9 long months, and I can't thank you enough! THANK YOU, THANK YOU, THANK YOU SO MUCH! :-) Greg

[Cucho]

You're welcome.
So this solved your issue as well:
Range("A1:A1").Copy
Range("A1:A1").CopyPicture

The error is very random and erratic. Depends on your computer.
In my case, apparently a computer with x64 bits processor is able to skip it but not in the case of other one with x32 bits.
Got me nuts for months as well.

[gmerrick]

You're welcome.
So this solved your issue as well:
Range("A1:A1").Copy
Range("A1:A1").CopyPicture

The error is very random and erratic. Depends on your computer.
In my case, apparently a computer with x64 bits processor is able to skip it but not in the case of other one with x32 bits.
Got me nuts for months as well.

I have a number of x64 systems, and the problem will come and go at random. Some days all our systems will work, some days one or two will work. I thought perhaps it was the version of Excel, which auto updates from time to time under Office 365, but found identical versions acting differently. It is a bizarre problem and I'd given up being able to solve it. Now it's finally solved!

[Antal]

Hi,
I have the same problem, the case is that if what is executed line a line with F8 all correct, but when I run
it normally fails every time a site and what is always the same code and data. I have tried your method but it still
gives me error ... I am desperate
This is my code:

Sub Situacion_actual_comparativa_PPT(diapositiva As Integer)
On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
Set ppSlide = ppApp.ActiveWindow.View.slide
    slide = diapositiva
Sheets("9 - S.A. Propuesta").Visible = True
ppApp.ActiveWindow.View.GotoSlide (slide)
    Set ppSlide = ppApp.ActivePresentation.Slides(slide)
Range("J2").Select
    Range("J2").Copy
    Range("J2").CopyPicture Appearance:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select 
With ppApp.ActiveWindow.Selection.ShapeRange
        .IncrementLeft -206
        .IncrementTop -45
    End With
End sub

Now the fault gives me in the line that is in red.

Shapes.paste : Invalid request. Clipboard is empty or contains data which may not be pasted here.

Thanks !!!

[gmerrick]

I understand your frustration. Does this always fail or only sometimes? For me it's hit and miss, which makes it nearly impossible to troubleshoot. Did you try adding the delay loop? It will take longer to complete but at least it increases the chances the macro will run.

[Aussiebear]

@Antal, Have you tried removing the .Select from the line

[Antal]

I've tried everything and it fails. It is random 100%, sometimes it runs without problems, other times it fails every two lines ... there is no way, with F8 everything works.

[rlv]

Did you try adding the delay loop? It will take longer to complete but at least it increases the chances the macro will run.

Did your code work if you used DoEvents instead of a delay?

[gmerrick]

Hi,
I have the same problem, the case is that if what is executed line a line with F8 all correct, but when I run
it normally fails every time a site and what is always the same code and data. I have tried your method but it still
gives me error ... I am desperate
This is my code:

Sub Situacion_actual_comparativa_PPT(diapositiva As Integer)
On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
Set ppSlide = ppApp.ActiveWindow.View.slide
    slide = diapositiva
Sheets("9 - S.A. Propuesta").Visible = True
ppApp.ActiveWindow.View.GotoSlide (slide)
    Set ppSlide = ppApp.ActivePresentation.Slides(slide)
Range("J2").Select
    Range("J2").Copy
    Range("J2").CopyPicture Appearance:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select 
With ppApp.ActiveWindow.Selection.ShapeRange
        .IncrementLeft -206
        .IncrementTop -45
    End With
End sub

Now the fault gives me in the line that is in red.

Shapes.paste : Invalid request. Clipboard is empty or contains data which may not be pasted here.

Thanks !!!

I had an idea to use error trapping and it seems to work. I replaced all lines using CopyPicture with this construct:

On Error Resume Next
Cells(1, 1).CopyPicture 'THIS WAS THE ORIGINAL LINE
If Err.Number <> 0 Then
Cells(1, 1).CopyPicture
End If
On Error GoTo 0

It seems when the first attempt to execute would raise an error the second attempt always works... so far at least. Hope this works for you!

[mdmackillop]

Check out these recent threads.
http://www.vbaexpress.com/forum/show...-as-embed-link
http://www.vbaexpress.com/forum/show...l=1#post361516