VBA deleting a duplicate copy of chart object fails in Excel 2013

I have a VBA code that is intended to copy the contents of a range into a chart, to be able to export it to a PNG file (+some post-processing using an external command). Here is the relevant part:

Sub GenererImage()  ' Entry point
    getparams    ' Collect parameters and define global variables
    MiseEnPage.Range(ZoneImage).CopyPicture Appearance:=xlScreen,Format:=xlPicture
    Application.DisplayAlerts = False

    With ObjetGraphique.Duplicate
        .Chart.Paste
        .Chart.Export Filename:=CheminImage, Filtername:="PNG"
        .Select
        .Delete
    End With
    Application.DisplayAlerts = True
End Sub

The getparams procedure called in there is just collecting some parameters from another worksheet to define:

This code used to work perfectly in Excel 2010. Now my company has deployed Excel 2013 and my code now fails on the .Delete line, leaving the copy of the ChartObject (with the range picture pasted inside it) on the sheet and stopping macro execution.

I have tried activating the worksheet first, selecting the duplicate prior to deleting it and other things, to no avail. When tracing the execution in the debugger it chokes on the delete line with error 1004.

I am frustratingly stuck. Any clue?

Upvotes: 1

Views: 558

Answers (2)

OK after fiddling a lot with the object model, here is (the relevant part of) my final solution. Many thanks to HarassedDad for the clues.

Sub GenererImage()  ' Point d'entrée
    getparams
    MiseEnPage.Range(ZoneImage).CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Application.DisplayAlerts = False
    With ObjetGraphique
        .Chart.Paste
        .Chart.Export filename:=CheminImage, Filtername:="PNG"
        .Chart.Shapes(1).Delete
    End With

    Application.DisplayAlerts = True
End Sub

What seems to happen is that the .Paste method of the Chart object creates a Shape in the .Shapes collection of this object. I can delete this Shape, but not the Chart itself or the ChartObject. Excel 2010 would allow that, but not Excel 2013.

I still do not understand the reasons, but at least I have something that works (until the next excel update probably...).

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

If this works

 With ObjetGraphique.Duplicate
    .Chart.Paste
    .Chart.Export Filename:=CheminImage, Filtername:="PNG"
    .Select
End With
Selection.Delete

we have to assume that either the With is holding a reference and preventing the delete, or that the delete routine called by the selection object is not the same delete that's called by ObjetGraphique.Duplicate.delete, or that it's a subtle timing bug and that the extra time it takes to retrieve the selected object is enough to fix it.

Upvotes: 0

Related Questions