Save Workbook Chart as Images

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
106
Office Version
365
Platform
Windows
Hi,

I'd like to be able to save all the workbook charts as images.

The folder structure would ideally be like this:

C:\Users\Pictures\Workbook Name\Sheet Name\Chart Title Name

Some code is below which I've started on but am a bit stuck.

Any help would be appreciated thanks.

VBA Code:
Sub ExportSheetCharts()

'Create a variable to hold the path and name of image
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Dim strUserName As String
Dim subfolder As String
Dim strFileFullName As String


strUserName = Application.UserName
subfolder = ActiveSheet.Name
strFileFullName = ThisWorkbook.FullName
 
If Len(Dir("C:\Users\" & strUserName & "\Pictures\" & strFileFullName & "\")) = 0 Then
 MkDir ("C:\Users\" & strUserName & "\Pictures\" & strFileFullName & "\")
End If


'Export the chart
           For Each sht In ActiveWorkbook.Worksheets
           For Each cht In ActiveSheet.ChartObjects
           cht.Chart.Export "C:\Users\" & strUserName & "\Pictures\" & strFileFullName & "\" & subfolder & cht.Chart.ChartTitle.Text & x & ".jpg"
           x = x + 1
       Next cht
       Next sht

End Sub
 
Last edited:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
765
I don't see anything immediately and obviously wrong with your code. Why don't you explain what you mean by "but am a bit stuck"?
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
106
Office Version
365
Platform
Windows
Sure there are a couple of things for some reason after the Workbook named directory is created it then has an error if it's run.

Also creating separate folders for each tab name is quite tricky as I'm not sure where to put that in the code, and it would also have to recognise if those folders are already created it doesn't need to create them again.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
765
A good rule of thumb when asking for help with code is: Don't make people guess. There are many, many errors that excel is capable of generating so avoid general statements like "it has an error". If you get an error, describe which specific error you are getting. All runtime errors have a number and a description. If the error occurs at a specific line of code, tell us which line of code.
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
106
Office Version
365
Platform
Windows
Okay understand your point, I'll provide more detail tomorrow morning.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,364
Messages
5,468,189
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top