Exporting ranges as images in excel 2010

hello,

I'm trying to export a range of cells in Excel to a PNG file using VBA.

I have a script that does that job, but when it resizes the chart object with " .Height = PicTemp.Height + 8" it squashes the table too... I can't see how I prevent this happening, essentially I just want the chart element with no white space - sized as it was in the Excel sheet.

Output screenshot attached.

I'm working with 2010.

Thanks

Sub ExportCellsAsPicture()
    Const FName As String = "C:\temp\SavedRange.jpg"
    Dim pic_rng As Range
    Dim ShTemp As Worksheet
    Dim ChTemp As Chart
    Dim PicTemp As Picture
    Application.ScreenUpdating = False
    Set pic_rng = Worksheets("Sheet1").Range("B3:G7") 'Set your range here
    Set ShTemp = Worksheets.Add
    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
    Set ChTemp = ActiveChart
    pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    ChTemp.Paste
    Set PicTemp = Selection
    Range("B1").Select
        With ChTemp.Parent
        .Width = PicTemp.Width + 8
        .Height = PicTemp.Height + 8
    End With
    
    
    
    ChTemp.Export Filename:="C:\temp\SavedRange.PNG", FilterName:="PNG"
    Application.DisplayAlerts = False
    ShTemp.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Open in new window

SavedRange.PNG
bedsingarAsked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Hi

Add this  
If PicTemp.Placement <> 3 Then PicTemp.Placement = 3

Open in new window

after Set PicTemp = Selection


Kris
0
 
bedsingarAuthor Commented:
Perfect thanks

Josh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.