How to Save Duplicate Images in Excel as Different JPEGs

jpegmicrosoft-excel-2007pictures

I have an Excel sheet with images which are stored as objects in one column and product names in another. I was trying to extract all the images to JPEG files. I was able to achieve this by saving the Excel sheet as an HTML document.

This gave me the following files

  • A HTML file
  • A folder with all the images together with a XML file filelist.xml with a list of all image files

Now I need to create a new Excel sheet which has the product names in one column and image file names in the other. I was able to achieve this using the created XML file filelist.xml.

However, this has introduced a new problem

My original Excel sheet had certain variants of products for e.g. (Product1 white, Product1 green, Product1 red, Product1 blue)

While the products are a little different in color, the associated image object was repeated. Basically all four had the same corresponding image. Now while saving this file as an HTML I found only one image file instead of four.

Also the XML file had only one entry instead of four and so the new Excel sheet I'm creating from the XML also has one entry instead of four.

Though the same object is used four times for similar products, they seem to be stored with different names. (Clicking on the 1st showed "picture1", the second "picture2" etc). This has given me hope that maybe there is a way to save images individually so I receive four JPEGs instead of one.

Best Answer

(both solutions are superior to the extract method because redundant images are exported as well)

Solution #1

Here is my VBA macro to export all pictures from a workbook.
The pictures will be saved to a folder of your choice and Excel's internal object name is used as picture filename. The code is commented and not that difficult.

Sub ExportPictures()
    '## 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
    For Each WS In ThisWorkbook.Sheets
    For Each PIC In WS.Shapes

        '## create a chart with same dimensions as current picture
        '## subtract 0.5px from chart dimensions to avoid a strange border
        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

        '## copy the picture into chart. Only a chart could be exported
        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

How to use

  • Open your Excel workbook and the VBA editor with Alt+F11
  • Paste the code somewhere and run it with F5 in the editor

Used resources

Solution #2

Use Graphics Exporter (Excel Add-In)

This add-in will allow you to export pictures, shapes, charts and more to image files.

enter image description here

64-bit problem

Even the new version for Office 2007 is compiled for 32-bit systems. It won't run on a 64-bit Windows unless you fix the code like explained on Stackoverflow.
I've done those small fixes for you. Download the 64-bit version here.

Related Question