Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

(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.

share|improve this answer
    
This is by far the most organized and helpful answer I have ever received. I tried both methods and both work perfectly. However there is one thing I forgot to specify, I have about 5500 images in that excel sheet, thus the first solution(VB script) ended up taking more time, and CPU usage, however the second one worked perfectly fine. Thanks once again for taking the time to not only answer my question, but also make it well organized and easy to understand. –  TDsouza Jul 27 '13 at 5:27
    
Uh, I'm curious. Can I have a look at that Excel file with 5500 so I can try to speed up the script? Of course only, if your workbook doesn't contain sensitive personal or business data. –  nixda Jul 27 '13 at 8:03
    
Oh, Im really sorry but the excel sheet does contain some sensitive data. Moreover the data does not belong to me, but my client. I'l still try and see if I can work something out. :) –  TDsouza Jul 29 '13 at 7:37
add comment

Download OpenOffice or better LibreOffice, and install it. You can use the install-on-a-stick version if you don't want to mess with your system. Open the document in Calc. Save it as ods. Open Windows Explorer. Change the extension .ods to .zip. Then unzip the file. There is an images folder inside with all images, full quality, not scaled, probably as PNG, which is better quality than JPEG.

PS: maybe my solution is not exactly what you asked for, but for me this is the easiest way to get all images out of an MS Office document. It works for Word as well, probably Powerpoint too.

share|improve this answer
4  
Or you can skip the whole OO/LO workaround and do the same thing to an xlsx file, the approach is the same. Rename to .zip, extract, browse the resulting structure for the image files. You might find two files per image there, one is full res. –  TheUser1024 Jul 26 '13 at 9:10
    
Thanks! Good to know! –  rxt Jul 26 '13 at 11:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.