Excel and PowerPoint 2016 for Mac gripes

I’ve been trying to maintain compatibility between Mac and Windows versions of Excel/VBA apps for a while now, and it’s a pain.
These notes refer to versions 16.14 of both Excel and Powerpoint.

Here are some of my recent threads on answers.microsoft.com

Passing a user-defined object to a sub As Object passes an object with all Null properties. Passing As MyClass works. Methods work, just not properties.
Interoperability between Excel 2016 for Mac and Powerpoint 2016 for Mac is very frustrating.
To get VBA to copy a picture of a chart to a slide, I’ve tried four different ways.
Only in the latest Insider Build 16.14 does the obvious Copy / Paste method work but even that requires copying and pasting as an image in Excel first and then copying amd pasting the static picture.

1) Manual copy and paste works, as it does in Windows. I want to automate this for what could be 100 slides.

2) The CopyPicture method fails for all objects. Range gives error 1004 CopyPicture method of Range class failed. ChartArea gives error 438 Object doesn’t support this property or method. Chart.Copypicture the result when pasting is a shape with text “The picture can’t be displayed”. (Update 8-May-18: Excel 16.14 (180507) Chart.Copypicture now works, includes a TIFF format in the copied data. And to save a picture of a range as a file, we can now do range.copypicture followed by adding a temporary chart by chartobjects.add and then chartobject.chart.paste of the picture, then chart.export as PNG)

See my post on answers.microsoft.com.
To work around this, create the picture in Excel:

 ' ws is the target sheet for the picture
 ws.Cells(30, 9).Select 'below the chart; to anchor the Paste
 ws.PasteSpecial 'default Format Link:=False,DisplayAsIcon:=False
 'Format: 0=PNG,1=JPEG,2=GIF,3=Enhanced metafile,4=Bitmap,5=DrawingObject
 ' but UI offers only PDF/TIFF/Drawingobject
 Set oShp = ws.Shapes(ws.Shapes.Count)


3) There is no Slide.PasteSpecial method in Mac Powerpoint VBA. Slide.Paste takes NO data type argument.

4) ApplescriptTask can be used to execute the Paste in Powerpoint. Thanks to Ron de Bruin for help here.

This code is in the VBA:

    AppleScriptTask "CopyPasteScript.applescript", "PastePowerpoint", ""

Save this code in a file eg “CopyPasteScript.applescript” in the special folder
/Users/myusername/Library/Application Scripts/com.microsoft.Excel

    on PastePowerpoint()
    -- Paste into Powerpoint
    tell application "Microsoft PowerPoint" to activate
    delay 0.1
    tell application "System Events"
    tell process "microsoft powerpoint" to keystroke "v" using command down
    end tell
    end PastePowerpoint

5) Another method is to export the Chart as a picture and then import it in PowerPoint

    Dim oChart As Chart, sFile As String
    Set oChart = ThisWorkbook.Worksheets(1).ChartObjects(1).Chart
    oChart.Parent.Activate ' Chartobject
    sFile = ThisWorkbook.Path & Application.PathSeparator & "test.png"
    oChart.Export sFile, "png" 
    ' Only works on Excel for Mac 16.14 or later; 16.9 to 16.13 produces zero bytes file
    oSlide.Shapes.AddPicture(sFile, False, True, 185, 110, -1, -1)


6) In practice, the above method is agonisingly slow, so what I really do is write a VBA script with lines that create a presentation, add slides, set the title text, and call AddPicture using the file names created.
Then I create a temporary presentation in PowerPoint, import that VBA code, execute it, and close the temporary presentation without saving it.

7) Before Chart.Export started to work in Excel 16.14 or later, I did try the ugliest workaround, and it works but it’s really a kludge.

7.1 Create a temporary workbook with a sheet for each slide and copy/paste the title and chart image into it. Add needed properties to the .AlternativeText of the pictures.

7.2 Save the workbook as html.

7.3 Open the tabstrip.htm file, read the text, split it at “<a href=” and extract the tab names.

7.4 for each tab file name, open its htm file, read the text, split it at “<v:shape id=” and parse out the picture file names and properties.

7.5 Now I have the picture file name, add a line to the PowerPoint VBA file I create in (6) above to add a slide using the right title and picture filename.
Horrible, isn’t it?


About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Excel/VBA, Mac, powerpoint, Uncategorized. Bookmark the permalink.

6 Responses to Excel and PowerPoint 2016 for Mac gripes

  1. Matt says:

    Hi Patrick. I am interested in possibly hiring you to help me with an Excel VBA Mac project. Could you email me and I can tell you more about it?

  2. Avi says:

    I tried to call CopyPasteScript from Excel 2019 VBA and it did not work.
    Is it supported in mac OS 10.14 & Excel 2019 ?
    Thank you for your help

  3. Have a look at the link to Ron de Bruin’s instructions
    The command may be now disabled on the Mac. See also
    I don’t use that script. I now save all the pictures I want to paste as files, write VBA code to create the presentation to a file and import it to the Powerpoint instance and run it there.

    Set mPPAppObject = CreateObject("PowerPoint.Application")
    Set mPPPres = mPPAppObject.Presentations.Add ' a blank presentation to hold the VBA
    Set comp = mPPPres.VBProject.VBComponents.Import(FilenameofVBAcode)
    mPPAppObject.Run "Mainprocname"
  4. Ron says:

    Hi all, if you like to have trouble use Mac Office, see also http://www.rondebruin.nl/mac/mac034.htm

    And with Apple sandbox you can also have problems, see http://www.rondebruin.nl/mac/mac034.htm

    Every update there is a surprise, Will work more on Mac Office this summer and update my site with more information.

    Have fun

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s