none
MS Office Automation..Intermittent Error: Excel cannot paste data RRS feed

  • Question

  • I've read most of the posts under this title, but I am having this issue in a different context.  I'll try to be concise in describing the situation.

    I'm building a generator for our annual financial report.  The data is in SQL Server.  The presentation will be a Word document.  The Word document will contain several dozen linked Jpeg images that are created from a set of data charts in Excel.  Each chart is simply a small table of data about a particular topic--usually 8 to 10 columns wide and 10-15 rows.  A few cells are merged in most of the charts.

    An Excel macro selects the range containing a chart, copies the range, then pastes the range as a picture.  It then copies the picture and pastes it into a new Excel Chart with is then exported as a Jpeg file.

    To control all this I'm using Access to get and process the data from SQL Server, drop individual values into the applicable cells in the Excel file that contains the "camera-ready" charts (Figures 1-60ish), and then call the Excel macro described above to make the Jpeg files.  All of this works great...except sometimes.

    Intermittently, after Access passes control to Excel, during execution of the Excel macro, Excel throws the "Excel cannot paste data" error.

    Here's the snippet of VBA code that is misbehaving:

          ThisWorkbook.Worksheets(FigNumber).Activate
          ActiveWindow.Zoom = 200
          With ActiveSheet.Shapes
            .AddChart.Select
            .Item(1).Line.Visible = msoFalse
            .Item(1).Width = Range(FigLoc).Width
            .Item(1).Height = Range(FigLoc).Height
            .Item(1).Top = 2800
          End With
          
          Range(FigLoc).Select
          Application.CutCopyMode = False
          Selection.Copy
          Range("BZ2").Select
          ActiveSheet.Pictures.Paste.Select
          Selection.Copy
          ActiveSheet.Shapes.Item(1).Select
          ActiveChart.Paste
          ActiveChart.Export (Application.ThisWorkbook.Path & "\FigureImages\" & FigImg)


    The error comes at the fifth line up from the bottom: ActiveSheet.Pictures.Paste.Select.

    Access calls this Excel macro once for each Figure.  On a typical run, of 58 Figures, about 3 will throw this error--always at the same line.  The figures that error out are different each time.  With no change to anything, a given figure may work fine one time and error out the next, or vise versa.

    Also noteworthy, when the error happens and I go into the Excel VBA debugger, if I click the little green triangle on the toolbar to resume execution, execution does resume, and the line that caused the error--opening the debugger--executes just fine as if the error never happened.

    I'm thinking it's some kind of timing/hand-shaking issue where Excel is getting too many instructions too fast from Access and tripping over itself trying to keep up, but that's just my gut.  I haven't thought of a way yet to really find out what's happening.

    The good news is it gives good output; the bad news is I can't release it to my users until I quash this elusive little devil of a bug.  I will be very grateful for any suggestions.

    Jeff


    Thursday, June 22, 2017 10:34 PM

Answers

  • Hi jDama,
    I can reproduce your issue. I created a workbook with ten worksheet. I fill out some simple date in A1 to J15 and merged some cells and run below code again and again to test code. There is very few chance this error happens.
    For i = 1 To 10
          ThisWorkbook.Worksheets(i).Activate
          ActiveWindow.Zoom = 100
          With ActiveSheet.Shapes
            .AddChart.Select
            .Item(1).Line.Visible = msoFalse
            .Item(1).Width = Range("A1:J15").Width
            .Item(1).Height = Range("A1:J15").Heigh
            .Item(1).Left = Range("A1:J15").Width
            .Item(1).Top = 0
          End With 
          Range("A1:J15").Select
          Application.CutCopyMode = False
          Selection.Copy
          Range("A20").Select
          On Error Resume Next
          ActiveSheet.Pictures.Paste.Select
          If Err.Number = 1004 Then
            Debug.Print "There is an error!"
          End If
          Selection.Copy
          ActiveSheet.Shapes.Item(1).Select
          ActiveChart.Paste
          FigImg = "Test.jpg"
          ActiveChart.Export (Application.ThisWorkbook.Path & "\FigureImages\" & FigImg)
         Next i
    As you said, it could works when we continue to run the code in debugger. As a workaround, I re-paste the picture when the error happens. In my test, it works well. Here is the example.

          On Error Resume Next
          ActiveSheet.Pictures.Paste.Select
          If Err.Number = 1004 Then
            ActiveSheet.Pictures.Paste.Select
          End If
    Besides, I also suggest you copy the range as a picture and paste to the chart directly, so we don’t need run the code ActiveSheet.Pictures.Paste.Select to paste picture to the Cell(“BZ2“).
    Here is the example.
          Range("A1:J15").Select
          Application.CutCopyMode = False
          Selection.CopyPicture
          ActiveSheet.Shapes.Item(1).Select
          ActiveChart.Paste
    Best Regards,
    Terry
    • Marked as answer by jDama Wednesday, June 28, 2017 4:52 PM
    Tuesday, June 27, 2017 10:29 AM
  • Thanks so much for your time and suggestions!

    I have not tried the proposed solution yet, but it does make sense and I believe it would work.  I particularly like the idea of copying the range as a picture and pasting it to the chart directly.  I will try that as soon as I can, and would like to make that part of the final draft.

    In the mean time, I've tried something else on a whim that seems to work.  I lightly peppered the problem code with DoEvents statements.  It seems to slow execution down by an insignificant amount--likely just enough for Excel to keep up--and it appears to have eliminated the bad behavior.  Right now I have the program in a loop creating all 58 images over and over.  Just this moment it completed 20 iterations of the loop and there have been no errors at all.

    • Marked as answer by jDama Wednesday, June 28, 2017 5:04 PM
    Wednesday, June 28, 2017 5:04 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, June 23, 2017 9:16 AM
  • Hi jDama,
    I can reproduce your issue. I created a workbook with ten worksheet. I fill out some simple date in A1 to J15 and merged some cells and run below code again and again to test code. There is very few chance this error happens.
    For i = 1 To 10
          ThisWorkbook.Worksheets(i).Activate
          ActiveWindow.Zoom = 100
          With ActiveSheet.Shapes
            .AddChart.Select
            .Item(1).Line.Visible = msoFalse
            .Item(1).Width = Range("A1:J15").Width
            .Item(1).Height = Range("A1:J15").Heigh
            .Item(1).Left = Range("A1:J15").Width
            .Item(1).Top = 0
          End With 
          Range("A1:J15").Select
          Application.CutCopyMode = False
          Selection.Copy
          Range("A20").Select
          On Error Resume Next
          ActiveSheet.Pictures.Paste.Select
          If Err.Number = 1004 Then
            Debug.Print "There is an error!"
          End If
          Selection.Copy
          ActiveSheet.Shapes.Item(1).Select
          ActiveChart.Paste
          FigImg = "Test.jpg"
          ActiveChart.Export (Application.ThisWorkbook.Path & "\FigureImages\" & FigImg)
         Next i
    As you said, it could works when we continue to run the code in debugger. As a workaround, I re-paste the picture when the error happens. In my test, it works well. Here is the example.

          On Error Resume Next
          ActiveSheet.Pictures.Paste.Select
          If Err.Number = 1004 Then
            ActiveSheet.Pictures.Paste.Select
          End If
    Besides, I also suggest you copy the range as a picture and paste to the chart directly, so we don’t need run the code ActiveSheet.Pictures.Paste.Select to paste picture to the Cell(“BZ2“).
    Here is the example.
          Range("A1:J15").Select
          Application.CutCopyMode = False
          Selection.CopyPicture
          ActiveSheet.Shapes.Item(1).Select
          ActiveChart.Paste
    Best Regards,
    Terry
    • Marked as answer by jDama Wednesday, June 28, 2017 4:52 PM
    Tuesday, June 27, 2017 10:29 AM
  • Thanks so much for your time and suggestions!

    I have not tried the proposed solution yet, but it does make sense and I believe it would work.  I particularly like the idea of copying the range as a picture and pasting it to the chart directly.  I will try that as soon as I can, and would like to make that part of the final draft.

    In the mean time, I've tried something else on a whim that seems to work.  I lightly peppered the problem code with DoEvents statements.  It seems to slow execution down by an insignificant amount--likely just enough for Excel to keep up--and it appears to have eliminated the bad behavior.  Right now I have the program in a loop creating all 58 images over and over.  Just this moment it completed 20 iterations of the loop and there have been no errors at all.

    • Marked as answer by jDama Wednesday, June 28, 2017 5:04 PM
    Wednesday, June 28, 2017 5:04 PM
  • Follow up:  I just now tried your suggestion of pasting the picture to the chart directly.  That works smoothly and also solves another problem.  With the DoEvents statements I mentioned above, I had eliminated the problem of the error messages, however when the charts were exported, sometimes the export appeared to be happening prior to the paste!  I would see the chart container in the JPEG, but the picture wasn't there.  Copying the range as a picture and then pasting it directly into the chart before exporting seems to have fixed this.  Again, thank you very much for your suggestion!
    Wednesday, August 2, 2017 4:34 PM