CODEWITHSUNDEEP
vbaexcel
Andrew Perry
Andrew Perry

Reputation: 765

Trying to convert a chart in Excel into a picture to export that sheet away from its source data

The saga of the steel plate calculator continues. All the calculations work really well, to no small extent thanks to you lot here on SO, but in the final export stage I find that the graph showing utilisation optimisation loses its data if the source file is no longer open.

I'm looking, then, for a way to keep the graph static after export, ideally without having to copy the data fields across. The ideal would be to convert it into a picture, maintaining its location and size.

I found this here on SO, but it creates a new graph shape, apparently formatted as a pie chart:

Sub PasteGraph2()
Dim ws As Worksheet
Dim cht As Chart

Set ws = ActiveSheet
Set cht = ws.Shapes.AddChart.Chart
With cht
    .SetSourceData ws.Range("$B$21:$C$22")
    .ChartType = xl3DPie
    .ChartArea.Copy
End With
ws.Range("A2").PasteSpecial xlPasteValues
cht.Parent.Delete
End Sub

I also tried this, found on a site of Powerpoint macros and modified to fit, but unsurprisingly it doesn't work in Excel ("ppPastePNG - variable not defined").

Sub PasteGraph1()
' PasteGraph Macro

Dim oGraph As Shape
Dim oGraphPic As Shape
Dim dGrpLeft As Double
Dim dGrpTop As Double

oGraph = ActiveSheet.ChartObjects("Chart 3").Copy
    dGrpLeft = oGraph.Left
    dGrpTop = oGraph.Top
    oGraph.Copy
    ActiveSheet.Shapes.PasteSpecial DataType:=ppPastePNG
    Set oGraphPic = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    oGraph.Delete
    oGraphPic.Left = dGrpLeft
    oGraphPic.Top = dGrpTop

End Sub

The latter (PasteGraph1) seems to suit my purposes better, but how do I make it work? Is there a simpler way?

Upvotes: 0

Views: 2143

Answers (2)

Zwo
Zwo

Reputation: 1113

ppPastePng is a vba Variable for PowerPoint, so it is not defined in VBA for Excel.

This should work :

ActiveSheet.ChartObjects("Chart 3").Chart.CopyPicture xlScreen, xlBitmap
ActiveSheet.Paste

Upvotes: 1

Andrew Perry
Andrew Perry

Reputation: 765

(Added as answer for completeness, accepted @ZwoRmi's answer because it seems churlish not to given that his suggestion proved vital to making it work...)

Many thanks to @ZwoRmi for the key to solving this - here is the code I ended up using, which is a combination and tweak of the original PasteGraph1 approach and @ZwoRmi's much more useful copy method.

Sub PasteGraph1()
' Converts live graph to static image

Dim oGraphPic As Shape
Dim dGrpLeft As Double
Dim dGrpTop As Double

    dGrpLeft = ActiveSheet.ChartObjects("Chart 1").Left
    dGrpTop = ActiveSheet.ChartObjects("Chart 1").Top

    ActiveSheet.ChartObjects("Chart 1").Chart.CopyPicture xlScreen, xlBitmap
    ActiveSheet.Paste
    ActiveSheet.ChartObjects("Chart 1").Delete
    Set oGraphPic = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    oGraphPic.Left = dGrpLeft
    oGraphPic.Top = dGrpTop

End Sub

Upvotes: 0

Related Questions