Exporting All Graphics

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.

Sub SaveAllGraphics()
    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.Save
    ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Workbooks.Open FileName
   
‘   Delete the HTML file
   Kill TempName
   
‘   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
        Loop
    End If
‘   Show the exported graphics folder
   Shell “explorer.exe “ & DirName, vbNormalFocus
End Sub

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.

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *