jeudi 16 avril 2015

VBA exporting image from excel with right size

I have an excel sheet that contains some images. This a sample : enter image description here


I found this vba code to export the images to the hard drive(link).



Sub test()
'## Open file dialog to choose a destination folder
Set FOLDER = Application.FileDialog(msoFileDialogFolderPicker)
FOLDER.AllowMultiSelect = False
FOLDER.Show

'## loop through all sheets and all pictures
Dim oldWidth As Integer
Dim oldHeight As Integer
For Each WS In ThisWorkbook.Sheets
For Each PIC In WS.Shapes

Set CH = WS.ChartObjects.Add(1, 1, PIC.Height, PIC.Width)

'## save & temporarly disable the picture border
PIC.Select
PICBORDER = Selection.Border.LineStyle
Selection.Border.LineStyle = 0
PIC.Copy

CH.Chart.ChartArea.Select

CH.Chart.Paste

'## re-enable the old picture border
PIC.Select
Selection.Border.LineStyle = PICBORDER

'## export the chart as JPG. Change JPG to PNG if desired
CH.Chart.Export Filename:=FOLDER.SelectedItems(1) & "\" & PIC.Name & ".jpg", FilterName:="JPG"

'## delete chart to clean up our work
CH.Cut

Next PIC
Next WS


End Sub


This code works pretty well except that the images exported are too small and contains empty space (image displayed with win builtin viewer)


enter image description here


What did I miss ?


Aucun commentaire:

Enregistrer un commentaire