• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1434
  • Last Modified:

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
0
bedsingar
Asked:
bedsingar
1 Solution
 
krishnakrkcCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now