How to remove image borders when exporting from Excel?
DT
Here is an image of my code exporting from excel to file
For Each oShape In ActiveSheet.Shapes
strImageName = oShape.TopLeftCell.Row & "_" & oShape.TopLeftCell.Column
If oShape.Type = msoPicture Then
oShape.Select
'Picture format initialization
Selection.ShapeRange.PictureFormat.Contrast = 0.5: Selection.ShapeRange.PictureFormat.Brightness = 0.5: Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic: Selection.ShapeRange.PictureFormat.TransparentBackground = msoFalse: Selection.ShapeRange.Fill.Visible = msoFalse: Selection.ShapeRange.Line.Visible = msoFalse: Selection.ShapeRange.Rotation = 0#: Selection.ShapeRange.PictureFormat.CropLeft = 0#: Selection.ShapeRange.PictureFormat.CropRight = 0#: Selection.ShapeRange.PictureFormat.CropTop = 0#: Selection.ShapeRange.PictureFormat.CropBottom = 0#: Selection.ShapeRange.ScaleHeight 1#, msoTrue, msoScaleFromTopLeft: Selection.ShapeRange.ScaleWidth 1#, msoTrue, msoScaleFromTopLeft
'/Picture format initialization
Application.Selection.CopyPicture
Set oDia = ActiveSheet.ChartObjects.Add(0, 0, oShape.Width, oShape.Height)
Set oChartArea = oDia.Chart
oDia.Activate
With oChartArea
.ChartArea.Select
.Paste
.Export ("D:\images\" & strImageName & ".jpg")
End With
oDia.Delete 'oChartArea.Delete
End If
Next
The original image does not have a border, but in the resulting file there is an image with a large border:
How to keep the original image when exporting from Excel?
Sterilize
The border is on the chart from which the image is to be exported , not on the image itself. so:
Set oChartArea = oDia.Chart
'No need to Activate the ChartObject
With oChartArea
.ChartArea.Format.Line.Visible = msoFalse 'No Outline
.ChartArea.Format.Fill.Visible = msoFalse 'No Background
.ChartArea.Paste 'No need to use Select
.Export ("D:\images\" & strImageName & ".jpg")
End With
oDia.Delete
You can see this more clearly just by running ActiveSheet.ChartObjects.Add 0, 0, 100, 100
in the Immediate Window ( Ctrl+ in VBE G) and observing the ChartObject's default settings (including outlines) .