3 Methods to Batch Export All Charts in an Excel Workbook to a Windows Folder

You have an Excel workbook which contains a lot of charts spread in all of its Excel worksheets. Now, if you want to batch export all the charts to a Windows folder, you can use any of the 3 methods introduced in this article.

After creating multiple charts for the data in an Excel workbook, you may want to export these charts to a local folder as JPEG image files, too. In this case, of course, you can use the following Method 1 to manually save the charts one by one. Yet, it will be tedious in case of hundreds of worksheets and charts in the Excel file. So, here we will teach you the other 2 approaches, which can let you get it in bulk.

Method 1: Copy Charts to MS Word/PowerPoint

  1. For a start, select a chart in the Excel file.
  2. Then, right click on it and choose “Copy” option from the context menu.Copy Chart
  3. Next, create a new Word document.
  4. Later, click “Paste” button and select “Paste Special” option.
  5. In the popup dialog box, select “Picture (JPEG)” from the list of “As” and click “OK”.Paste Chart as JPEG Picture
  6. After that, pitch on the inserted picture in the document.
  7. Then, right click on it and choose “Save as Picture” option.Save Chart as Picture
  8. In the “File Save” window, enter a name and select target Windows folder.
  9. Lastly, click “Save”.Save Chart
  10. Now, you can use this way to export the other charts from the Excel file.

Method 2: Save Workbook as Web Page

  1. At the outset, in the opened Excel workbook, go to “File” and click “Save As”.
  2. Then, in the popup “Save As” window, choose “Web Page” in “Save as type”.Save Excel Workbook as Web Page
  3. Later, select a destination Windows folder and click “Save”.
  4. After that, browse to the folder “XXX_files”, in which you can see the exported images of charts.Exported Charts

Method 3: Batch Export via Excel VBA

  1. To begin with, launch Excel VBA editor according to “How to Run VBA Code in Your Excel“.
  2. Next, put the following code into a module or project.
Sub ExportAllCharts()
    Dim objShell As Object
    Dim objWindowsFolder As Object
    Dim strWindowsFolder As String
    Dim objSheet As Excel.Worksheet
    Dim objChartObject As Excel.ChartObject
    Dim objChart As Excel.Chart

    'Select a Windows folder
    Set objShell = CreateObject("Shell.Application")
    Set objWindowsFolder = objShell.BrowseForFolder(0, "Select a Windows folder:", 0, "")

    If Not objWindowsFolder Is Nothing Then
       strWindowsFolder = objWindowsFolder.self.Path & "\"

       For i = ThisWorkbook.Worksheets.Count To 1 Step -1
           Set objSheet = ThisWorkbook.Worksheets(i)

           If objSheet.ChartObjects.Count > 0 Then
              For Each objChartObject In objSheet.ChartObjects
                  Set objChart = objChartObject.Chart
                  objChart.Export strWindowsFolder & objChart.Name & ".jpg"
              Next
          End If
       Next

       'Open the windows folder
       Shell "Explorer.exe" & " " & strWindowsFolder, vbNormalFocus
   End If
End Sub

VBA Code - Batch Export All Charts in an Excel Workbook to a Windows Folder

  1. Then, press “F5” key to run this macro now.
  2. Next, select a Windows folder in popup dialog box.Select Windows Folder
  3. At once, the Windows folder will display, which contains the exported charts.Exported Charts in Windows Folder

Comparison

Advantages Disadvantages
Method 1 Easy to operate Very tedious if there are too many worksheets and charts
Method 2 Easy to operate Will save the other useless objects in this Excel workbook
Method 3 Very quick and convenient Users have to beware of external malicious macros

Repair Corrupted Excel Files

Excel files are vulnerable similar to other common files, such as Word documents. Therefore, it is recommended to keep a potent xls repair utility in vicinity, such as DataNumen Excel Repair. It will come in handy when your Excel file is damaged.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted sql and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.