Forum Discussion
VBA constantly crashing when trying to access Mac PowerPoint from Mac Excel
I've been trying to get a simple macro to update charts and tables in powerpoint from data in excel (the actual spreadsheet is much bigger - I've created a simple version here). This program works perfectly fine on the Windows version of Excel and Powerpoint and updates flawlessly. When I try to go to the Mac though it crashes both Excel Mac and PowerPoint Mac. I've tried all sorts of different types of code examples and I always end up with the same result.
Maybe I'm missing something simple in how I reference the data objects in PowerPoint? Any/all help would be greatly appreciated. It crashes the system the moment it tries to update the data directly (it opens the file no problem).
I've copied the code below (it won't let me upload a .xlsm file for some reason - i'm getting an error message) and a powerpoint with a few slides that it will update (note: I changed the names of the objects in the Windows version of PowerPoint for easy access).
Here's the code (the italicized bolded text is where it crashes). Note: if I stop the code right before that line and go to the immediate window I can do things like "? pppres.name" and it will give me the right result. but I were to say type in something simple like "? pppres.slides(1).name" then the whole thing also crashes (basically trying to get any data other than the name crashes it).
Sub UpdatePowerPoint()
Dim MyFile As String
#If Mac Then
MyFile = ActiveWorkbook.Path & "/New Sample Charts.pptx"
#Else
MyFile = ActiveWorkbook.Path & "\New Sample Charts.pptx"
#End If
Dim PPT As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim myShape As Object
Dim ws As String
Set PPT = New PowerPoint.Application
Set PPPres = PPT.Presentations.Open(Filename:=MyFile, ReadOnly:=msoFalse)
ws = "Sheet1"
‘ Let’s resize the chart first
nr = "A1:D5"
PPPres.Slides(2).Shapes("Chart1").Chart.ChartData.Workbook.Worksheets(1).ListObjects("Table1").Resize PPPres.Slides(2).Shapes("Chart1").Chart.ChartData.Workbook.Worksheets(1).Range(nr)
' Let's do the table
For x = 17 To 20
For y = 3 To 5
Row = x - 15
Data = Worksheets(ws).Cells(x, y).Value
PPPres.Slides(3).Shapes("Table1").Table.Cell(Row, y - 2).Shape.TextFrame.TextRange.Text = Data
Next y
Next x
' Let's do the pie chart
For x = 26 To 29
Row = x - 24
col = 2
Data = Worksheets(ws).Cells(x, 4).Value
PPPres.Slides(4).Shapes("Chart2").Chart.ChartData.Workbook.Worksheets(1).Cells(Row, col).Value = Data
Next x
Set PPPres = Nothing
Set PPT = Nothing
End Sub