CODEWITHSUNDEEP
vbaexcel
cpace
cpace

Reputation: 21

How to center pasted picture in excel chart with VBA?

I have my code below that takes in a range and converts it to a picture that I automatically save. The picture always is pasted in the top right. Is there a way to center the picture on the chart or even shrink the chart to fit only the picture size?

Sub topicture(Sendrng As range)

        Dim xcht As Chart
        Dim Sname As String
        Sname = ActiveSheet.Name
        Sendrng.CopyPicture xlScreen, xlPicture
        Set xcht = Charts.Add
        With xcht
            .ChartArea.ClearContents
            .Paste
            .ChartArea.Left = (xcht.ChartArea.Width - .Width) / 2
            .Export Filename:="CDrive\Photos\" & Sname & ".jpg", Filtername:="JPG"
            .Delete
        End With
End Sub

Upvotes: 0

Views: 901

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

You can resize the chart if you put it on a worksheet:

Sub tester()
    ExportRangePicture Range("B4:G20"), "C:\_Stuff\test1.jpg"
    ExportRangePicture Range("B4:G4"), "C:\_Stuff\test2.jpg"
End Sub


Sub ExportRangePicture(Sendrng As Range, fPath As String)
    Dim xcht
    Sendrng.CopyPicture xlScreen, xlPicture
    Set xcht = Sendrng.Parent.Shapes.AddChart
    With xcht.Chart
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        .Parent.Width = Sendrng.Width
        .Parent.Height = Sendrng.Height
        .Paste
        .Export Filename:=fPath, Filtername:="JPG"
        .Parent.Delete
    End With
End Sub

Upvotes: 2

Related Questions