Save Chart or Chart sheet as JPG

When you try to use VBA code that is working in Excel for Windows so save a Chart or Chart sheet as jpg it is possible you get errors when you use it in Mac Excel because of Apple sandbox requirements, the location where you save the file is the problem in most situations. Read the page below if you want to know why:

Problems with Apple’s sandbox requirements

See the two examples below that use a save location that is allowed. Note: Do not forget the copy/Paste the function named CreateFolderinMacOffice into your workbook.

The files will be saved in a folder named JPGFolder in the Microsoft Office Root folder, the second macro makes a picture of the chart sheet named "Chart1", the first macro makes a picture of an embedded chart named "Chart 1" on a sheet named "Sheet1".

Sub Save_Embedded_Chart_Mac_Excel()
'Ron de Bruin : 11-Dec-2020
Dim FolderName As String
Dim FileName As String
Dim Folderstring As String
Dim FilePathName As String

'Name of the folder in the Office folder
FolderName = "JPGFolder"
'Name of the jpg file
FileName = Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".jpg"

Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)
FilePathName = Folderstring & Application.PathSeparator & FileName

ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Export _
FileName:=FilePathName, FilterName:="GIF"

MsgBox "You find the JPG file in this location : " & FilePathName
End Sub


Sub Save_Chart_Sheet_Mac_Excel()
'Ron de Bruin : 11-Dec-2020
Dim FolderName As String
Dim FileName As String
Dim Folderstring As String
Dim FilePathName As String

'Name of the folder in the Office folder
FolderName = "JPGFolder"
'Name of the jpg file
FileName = Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".jpg"

Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)
FilePathName = Folderstring & Application.PathSeparator & FileName

ActiveWorkbook.Sheets("Chart1").Export _
FileName:=FilePathName, FilterName:="GIF"

MsgBox "You find the JPG file in this location : " & FilePathName
End Sub


Function CreateFolderinMacOffice(NameFolder As String) As String
'Function to create folder if it not exists in the Microsoft Office Folder
'Ron de Bruin : 13-July-2020
Dim OfficeFolder As String
Dim PathToFolder As String
Dim TestStr As String

OfficeFolder = MacScript("return POSIX path of (path to desktop folder) as string")
OfficeFolder = Replace(OfficeFolder, "/Desktop", "") & _
"Library/Group Containers/UBF8T346G9.Office/"

PathToFolder = OfficeFolder & NameFolder

On Error Resume Next
TestStr = Dir(PathToFolder & "*", vbDirectory)
On Error GoTo 0
If TestStr = vbNullString Then
MkDir PathToFolder
'You can use this msgbox line for testing if you want
'MsgBox "You find the new folder in this location :" & PathToFolder
End If
CreateFolderinMacOffice = PathToFolder
End Function
7-March-2021
Web design by Will Woodgate