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.
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
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
Set ChTemp = ActiveChart
pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set PicTemp = Selection
.Width = PicTemp.Width + 8
.Height = PicTemp.Height + 8
ChTemp.Export Filename:="C:\temp\SavedRange.PNG", FilterName:="PNG"
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True