One way to export all graphic images from a workbook is to save the file in HTML format. This creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and range images (created with the “camera” tool).
Here’s a VBA procedure that automates the process. It works with the active workbook.
Dim FileName As String
Dim TempName As String
Dim DirName As String
Dim gFile As String
FileName = ActiveWorkbook.FullName
TempName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name & “graphics.htm”
DirName = Left(TempName, Len(TempName) – 4) & “_files”
‘ Save active workbookbook as HTML, then reopen original
ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
Application.DisplayAlerts = False
‘ Delete the HTML file
‘ If Excel 2007, delete all but *.PNG files in the HTML folder
If Val(Application.Version) >= 12 Then
gFile = Dir(DirName & “*.*”)
Do While gFile <> “”
If Right(gFile, 3) <> “png” Then Kill DirName & “” & gFile
gFile = Dir
‘ Show the exported graphics folder
Shell “explorer.exe “ & DirName, vbNormalFocus
It starts by saving the active workbook. Then it saves the workbook as an HTML file, closes the file, and re-opens the original workbook. Next, it deletes the HTML file because we’re just interested in the folder that it creates (that’s where the images are). If you’re using Excel 2007, the code loops through the folder and deletes everything except the PNG files. Previous versions use a combination of GIF and PNG files, so this step is skipped if you’re using a pre-Excel 2007 version. Finally, it uses the Shell function to display the folder.
You’ll find that the quality of the images is much better in Excel 2007. But, unfortunately, pictures of ranges still look terrible.