none
Excel CopyPicture method of Range class only recently failing in VBA RRS feed

  • Question

  • I'm using Excel 2010 (14.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()
    Dim x As Long
    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.

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

    I've just discovered if I add a one second delay inside the loop it works properly:

    Application.Wait (Now + TimeValue("0:00:01"))

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

    Monday, February 29, 2016 4:14 PM

Answers

  • Hi GregBarbados,

    I've revised my code in order for real image to be copied.
    Would you try with my file?
    Excel_CopyPicture.zip

    by clicking [Copy Picture], five pictures in column [A] will be copied to column [B]




    If nothing improved about your issue, please try:

    [trial A]
      1) create/open a new workbook 
      2) copy code in the problematic book to standard module in the new book
      3) save the new book and check it

    [trial B]
      1) set/mount Office 2013 installation media (not 2016)
      2) execute "setup.exe"
      3) select "Repair" and [Continue]

    Regards.

    Wednesday, March 2, 2016 1:38 AM

All replies

  • Hi GregBarbados,

    I've tried with a new workbook, but could not reproduce your issue.
      My environment:
        1) Windows 8.1 Pro 64-bit, Excel 2013 32-bit
        2) Windows 10 Pro 64-bit, Excel 2016 32-bit
    Private Sub btn_CopyPicture_Click()
        Dim x As Long
        For x = 1 To 100
            Range("A1:A1").CopyPicture
        Next x
        MsgBox "Test completed !!"
    End Sub


    Are there any differences between your test and mine?

    Regards.
    Monday, February 29, 2016 11:57 PM
  • No difference.  I tried your code also... same issue.  I tried on another PC... works fine.  It's bizarre.
    Tuesday, March 1, 2016 11:53 AM
  • Hi GregBarbados,

    I've revised my code in order for real image to be copied.
    Would you try with my file?
    Excel_CopyPicture.zip

    by clicking [Copy Picture], five pictures in column [A] will be copied to column [B]




    If nothing improved about your issue, please try:

    [trial A]
      1) create/open a new workbook 
      2) copy code in the problematic book to standard module in the new book
      3) save the new book and check it

    [trial B]
      1) set/mount Office 2013 installation media (not 2016)
      2) execute "setup.exe"
      3) select "Repair" and [Continue]

    Regards.

    Wednesday, March 2, 2016 1:38 AM
  • I ran your test 5 times and every time it completes successfully!  I'll now start stripping down your code and see if I can recreate the failure I'm seeing.  I'll report back within a day or two.

    One major difference in my application is there is no picture object in the range I'm referencing in .CopyPicture.  It is just a formatted range of cells with text and numeric data.  I'm basically taking snapshots of various areas of one worksheet and creating a report made up of picture objects on a second worksheet.  Using pictures allows various height rows to be placed side by side on the second worksheet.  The application has worked fine for over a year... it just stopped working a few weeks ago.

    Thanks for your effort, it's both encouraging and greatly appreciated!

    Wednesday, March 2, 2016 2:29 AM
  • Hi GregBarbados,

    Have your issue been resolved? If you have, I suggest you mark the helpful reply as answer to close this thread.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, March 9, 2016 9:44 AM
  • I've tried this test many times, max value x = 13, never more. Usually x valuve 1 to 5 and I get error '1004': 
    Thursday, August 22, 2019 12:10 PM