1

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:

  • "MiseEnPage": reference to the worksheet object where the range I want to copy exists,
  • "ZoneImage" is set to the "B4:F11" string (refers to the range address),
  • "ObjetGraphique" is a reference to a ChartObject inside the "MiseEnPage" sheet. This ChartObject is an empty container (I am mainly using it to easily set the width and height).
  • "CheminImage" is a string containing the path to the picture filename on disk.

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?

braX
  • 11,506
  • 5
  • 20
  • 33
Frédéric Delacroix
  • 13
  • 2
  • What `ObjetGraphique` holds in watch window? – AntiDrondert Nov 16 '17 at 09:45
  • @AntiDrondert Not sure I understood your question, but here is what I can see in the "local variables" window about variable ObjetGraphique: - : ObjetGraphique : : ChartObject/ChartObject + : Application : : Application/Application + : Border : : Border/Border + : BottomRightCell : : Range/Range + : Chart : : Chart/Chart and many more properties which all look fine to me – Frédéric Delacroix Nov 16 '17 at 10:06
  • ObjetGraphique is set by the following line in the getparams procedure Set ObjetGraphique = MiseEnPage.ChartObjects(.Range(ObjetGraphique_param).Value) The .Range(etc) reduces to the string "Vide", which is the name of the actual object – Frédéric Delacroix Nov 16 '17 at 10:10
  • But what it's value on the line `.Delete` ? – AntiDrondert Nov 16 '17 at 10:49
  • The value of ObjetGraphique? it's still that ChartObject. I am applying the Delete method on the duplicate copy created by the line "With ObjetGraphique.Dupicate". – Frédéric Delacroix Nov 16 '17 at 11:01
  • I also tried to put that duplicate into another variable reference by saying Set dup=ObjetGraphique.Duplicate and then using dup.Chart.Paste, dup.Chart.Export (with appropriate parameters) and dup.Delete but the same error happens on line dup.Delete. Doing so dup is an object with type Shape, and it should support the Delete method according to the documentation. Still it fails. – Frédéric Delacroix Nov 16 '17 at 11:05
  • What happens if you try Selection.delete in the immediate window once it chokes? I wonder if the With is holding a reference and stopping the delete? – Harassed Dad Nov 16 '17 at 11:16
  • @HarassedDad : oh thank you it works ! I guess somehow the Delete is prevented by the fact that the reference is in the "With". Still I don't get why the trick with the "Set dup=ObjetGraphique.Duplicate" triggers the error since I removed the With block in that attempt. It does not explain either why it would work in Excel 2010 and not in Excel 2013. Well thank you very much ! Please submit your answer and I'll pick it. – Frédéric Delacroix Nov 16 '17 at 11:23

2 Answers2

0

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.

Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • It is not a timing problem, because the same problem happens when debugging step-by-step. – Frédéric Delacroix Nov 16 '17 at 15:00
  • Interesting - I've never heard of a problem with a With statement before, but the idea that they're different delete routines seems unlikely. – Harassed Dad Nov 16 '17 at 15:01
  • There is again a problem, I'm sorry for overlooking this the first time. If I use your code, it still fails. If I remove the .Select line then it does not stop with an error anymore. However, it deletes the graphics that was just pasted, but not the duplicate ChartObject container. I will have another try where I paste the graphics directly inside the original ObjetGraphique ChartObject and delete it from there. – Frédéric Delacroix Nov 16 '17 at 15:06
0

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...).

Frédéric Delacroix
  • 13
  • 2