Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Save Chart or Chart sheet as JPG file in Mac Excel

When you try to use VBA code that is working in Excel for Windows so save a Chart or Chart sheet as jpg it with error 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 and Mac Office 2016 with VBA code

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

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

Sub Save_Chart_Sheet_Mac_Excel()
    'Ron de Bruin : 21-Dec-2019
    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 = CreateFolderinMacOffice2016(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

Sub Save_Embedded_Chart_Mac_Excel()
    'Ron de Bruin : 21-Dec-2019
    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 = CreateFolderinMacOffice2016(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

Function CreateFolderinMacOffice2016(NameFolder As String) As String
    'Function to create folder if it not exists in the Microsoft Office Folder
    'Ron de Bruin : 1-Feb-2019
    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
    CreateFolderinMacOffice2016 = PathToFolder
End Function

If you want to make a picture of a chart and paste it on your worksheet see this page for example code : Paste Chart as Picture on sheet