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
- For a start, select a chart in the Excel file.
- Then, right click on it and choose “Copy” option from the context menu.
- Next, create a new Word document.
- Later, click “Paste” button and select “Paste Special” option.
- In the popup dialog box, select “Picture (JPEG)” from the list of “As” and click “OK”.
- After that, pitch on the inserted picture in the document.
- Then, right click on it and choose “Save as Picture” option.
- In the “File Save” window, enter a name and select target Windows folder.
- Lastly, click “Save”.
- Now, you can use this way to export the other charts from the Excel file.
Method 2: Save Workbook as Web Page
- At the outset, in the opened Excel workbook, go to “File” and click “Save As”.
- Then, in the popup “Save As” window, choose “Web Page” in “Save as type”.
- Later, select a destination Windows folder and click “Save”.
- After that, browse to the folder “XXX_files”, in which you can see the exported images of charts.
Method 3: Batch Export via Excel VBA
- To begin with, launch Excel VBA editor according to “How to Run VBA Code in Your Excel“.
- 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
- Then, press “F5” key to run this macro now.
- Next, select a Windows folder in popup dialog box.
- At once, the Windows folder will display, which contains the exported charts.
|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.
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
This is very helpful. Is there perhaps a way to apply a name to each exported chart that is using a cell reference from the data that created each chart/image?