Macro to open presentation and update charts

I am looking for a macro within Excel for Mac 2011 to open a presentation and update the charts that are in it. The charts that are in the presentation are located within the report in the 'Charts of SRs' tab. On the 'Table of Contents' tab, there is a button that says "Create Presentation" and I would like for the macro to be placed in there. The user will have both this report and powerpoint hand in hand but I want them to still be prompted to open the presentation to confirm that it is able to be updated. If they do not have the file or it can't be recognized, the user is informed that the presentation could not be updated.
DSE-Carelog-Report-V1.xlsm
DSE-Carelog-Slidedeck-Quarterly-Business
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I'm not able to open the power point presentation due to extension missing..Can you give a short name to powerpoint and then update it again..Don't update files with such a long names as extension goes missing..Just reduce the file name and when you update make sure the extension of the file is visible...
0
AckeemKAuthor Commented:
Attached is the slide deck as requested with a shorter name.
DSE-Carelog.pptx
0
Saurabh Singh TeotiaCommented:
Quick question you open to creating chart in PPT and saving the workbook their in the ppt only with only chart data as that will be comparatively faster and easy to update?

If you are not open to above solution then..do you want to paste the chart as a picture as shown in your ppt? or how do you want to paste that? Also would have  a template in place where you update these blank sides with new chart ??

In additional can you help me point out which chart will go to which slide ?? Point me like this column-A-M and rows-5-10 chart will go to slide-1 and so on..
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

AckeemKAuthor Commented:
So the reason the file is so big originally is because I would copy the charts over from the "Charts of SRs" tab and paste special "Microsoft Excel Chart Object". I was informed this is how it was done for a previous tool similar to this one.

Ideally, I would want to click the button "Create Presentation" and have that slidedeck open up and the charts update based on what data is in the "Charts of SRs" tab. If you look at this tab, you will see that it is aligned with the charts within the presentation. Not sure which way you feel would be most appropriate for a smaller file size but whichever solution you feel will accomplish this we can go with.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Try this AckeemK. It has been tested with your two latest files. You need to change the 4 Const values as required.

' ==============================================
' Macro to export charts from a worksheet to PowerPoint (runs from Excel)
' Dependencies :
' 1. Workbook with sheet containing charts to be exported
' 2. PowerPoint fiule in same folder (change name in PPTFile below)
' What is does:
' Opens existing PowerPoint report file and updates chart clides with charts from Excel sheet "Charts of SRs"
' Tested : PowerPoint 2011 (Mac) and 2013 (PC)
' Source : http://youpresent.biz/
' Author : Jamie Garroch
' Date : 17 May 2015
' ==============================================
Sub UpdatePowerPoint()
    ' Macro Constants - change as required
    Const iCharts = 10 ' Number of charts to export
    Const iSlideStart = 14 ' The first slide to update the chart
    Const sPPTFile = "DSE-Carelog.pptx" ' The name opf the PowerPoint file in the same folder as this Excel file
    Const sChartSheet = "Charts of SRs" ' The name of the worksheet containing the reports
    ' Declare late-binding objects so that a reference to the PowerPoint library isn't required
    Dim oPPT As Object  ' PowerPoint application
    Dim oPres As Object ' PowerPoint presentation
    Dim oSld As Object  ' PowerPoint Slide
    Dim oShp As Object  ' PowerPoint shape (to contain a chart object or image)
    ' Declare Excel objects
    Dim oWB As Workbook
    Dim oWS As Worksheet
    ' Declare variables
    Dim counter As Integer
    Dim FilePath As String
    Dim FileSeparator
    Dim ChartTop As Single, ChartLeft As Single, ChartWidth As Single, ChartHeight As Single ' position and dimensions of chart on slide
    
    MsgBox "About to update PowerPoint. Please wait for the confirmation message after clicking OK.", vbInformation + vbOK, "Updating PowerPoint"

#If Mac Then
    FileSeparator = ":"
#Else
    FileSeparator = "\"
#End If

    Set oWB = ActiveWorkbook
    
    FilePath = ActiveWorkbook.Path & FileSeparator
    
    ' Find the index of the sheet with the report charts
    For counter = 1 To oWB.Sheets.Count
        If oWB.Sheets(counter).Name = sChartSheet Then Exit For
    Next
    Set oWS = oWB.Sheets(counter)
    
    Set oPPT = CreateObject("PowerPoint.Application")
    Set oPres = oPPT.presentations.Open(FilePath & sPPTFile)
    
    For counter = 1 To iCharts
        Set oSld = oPres.slides(iSlideStart + counter - 1)
        For Each oShp In oSld.Shapes
            If oShp.Type = msoEmbeddedOLEObject Or oShp.Type = msoChart Then
                ChartTop = oShp.Top: ChartLeft = oShp.Left: ChartWidth = oShp.Width: ChartHeight = oShp.Height
                ' Delete the old chart from the slide
                oShp.Delete
                GoTo CopyChart
            End If
        Next
        
CopyChart:
         oWS.ChartObjects(counter).Copy
        ' You can use the .PasteSpecial method to paste using one of the picture formats instead of the chart object itself
        Set oShp = oSld.Shapes.Paste
        ' Resize and position the chart
        oShp.Top = ChartTop: oShp.Left = ChartLeft: oShp.Width = ChartWidth: oShp.Height = ChartHeight
    Next
    
    ' Clear PowerPoint objects
    Set oPPT = Nothing: Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
    ' Clear Excel objects
    Set oWB = Nothing: Set oWS = Nothing
    
    MsgBox "PowerPoint presentation updated.", vbInformation + vbOK, "Updated PowerPoint"

End Sub

Open in new window

0
AckeemKAuthor Commented:
Jamie,

Very thankful for the coding. I've double check the constants and after running the macro, I keep receiving a error saying "Application-defined or object-defined error and when I click debug it takes me to line 66. Any actions I'm missing here?

oWS.ChartObjects(counter).Copy
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Is your sheet with the charts still called 'Charts of SRs' and if not, change this line accordingly:

Const sChartSheet = "Charts of SRs"

Open in new window


Which version of Office are you using so I can double check?
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
I can reproduce on MSO 2013 (sorry, I only checked on Mac:2011). I will correct and repost changed code shortly.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Updated with the following changes:

1. Corrected the error you saw
2. Added checks for missing PowerPoint file and/or Excel charts sheet
3. Visible progress feedback for user as PowerPoint slides are updated
4. Pasting the smaller [read only] pictures of charts for file size and privacy efficiency

Checked and working with your two files on PowerPoint:mac 2011 and 2013.

Option Explicit

Private Enum PpWindowState
  ppWindowMinimized = 2
  ppWindowMaximized = 3
  ppWindowNormal = 1
End Enum

' Sub to start slideshow?
Sub CreatePresentation()
Dim sPath As String
Dim pShow As PowerPoint.Presentation
Dim pptfile As PowerPoint.Application

Set pptfile = CreateObject("powerpoint.application")
sPath = ActiveWorkbook.Path

Set pShow = pptfile.Presentations.Open(sPath & "\Slidedeck.ppt")
pShow.SlideShowSettings.Run
pptfile.Visible = True
End Sub

' =============================================================================================================
' Macro to export charts from a worksheet to PowerPoint (runs from Excel)
' Dependencies :
' 1. Workbook with sheet containing charts to be exported (change name in sChartSheet below)
' 2. PowerPoint file in same folder (change name in sPPTFile below)
' What is does:
' Opens existing PowerPoint report file and updates chart clides with charts from Excel sheet "Charts of SRs"
' Tested : PowerPoint 2011 (Mac) and 2013 (PC)
' Source : http://youpresent.biz/
' Author : Jamie Garroch
' Date : 17 May 2015
' =============================================================================================================
Sub UpdatePowerPoint()
    On Error Resume Next

    ' Macro Constants - change as required
    Const iCharts = 10 ' Number of charts to export
    Const iSlideStart = 14 ' The first slide to update the chart
    Const sPPTFile = "DSE-Carelog.pptx" ' The name opf the PowerPoint file in the same folder as this Excel file
    Const sChartSheet = "Charts of SRs" ' The name of the worksheet containing the reports
    ' Declare late-binding objects so that a reference to the PowerPoint library isn't required
    Dim oPPT As Object  ' PowerPoint application
    Dim oPres As Object ' PowerPoint presentation
    Dim oSld As Object  ' PowerPoint Slide
    Dim oShp As Object  ' PowerPoint shape (to contain a chart object or image)
    ' Declare Excel objects
    Dim oWB As Workbook
    Dim oWS As Worksheet
    ' Declare variables
    Dim counter As Integer
    Dim FilePath As String
    Dim FileSeparator
    Dim ChartTop As Single, ChartLeft As Single, ChartWidth As Single, ChartHeight As Single ' position and dimensions of chart on slide
    
#If Mac Then
    FileSeparator = ":"
#Else
    FileSeparator = "\"
#End If

    FilePath = ActiveWorkbook.Path & FileSeparator
    
    If MsgBox("About to update this PowerPoint presentation:" _
      & vbCrLf & vbCrLf & _
      sPPTFile _
      & vbCrLf & vbCrLf & _
      "With charts from this sheet:" _
      & vbCrLf & vbCrLf & _
      sChartSheet _
      & vbCrLf & vbCrLf & _
      "Please wait for the confirmation message after clicking OK.", _
      vbInformation + vbOKCancel, "Updating PowerPoint") = vbCancel Then Exit Sub

    Set oWB = ActiveWorkbook
    
    ' Get a reference to the charts sheet
    Set oWS = oWB.Worksheets(sChartSheet)
    If oWS Is Nothing Then MsgBox "Couldn't find the sheet : " & Chr(34) & sChartSheet & Chr(34), vbCritical + vbOKOnly, "Couldn't find sheet": Exit Sub
    
    ' Open the PowerPoint presentation
    Set oPPT = CreateObject("PowerPoint.Application")
    Set oPres = oPPT.Presentations.Open(FilePath & sPPTFile)
    If oPres Is Nothing Then MsgBox "Couldn't find the presentation : " & Chr(34) & FilePath & sPPTFile & Chr(34), vbCritical + vbOKOnly, "Couldn't find the presentation": Exit Sub
    
    ' Find the chart in the PowerPoint slide and get it's position and size before deleting it
    For counter = 1 To iCharts
      Set oSld = oPres.Slides(iSlideStart + counter - 1)
      ' Optionally show the slide to be updated to provide the user with visual feedback
      oPPT.ActiveWindow.View.GotoSlide oSld.SlideIndex
      For Each oShp In oSld.Shapes
        If oShp.Type = msoEmbeddedOLEObject Or oShp.Type = msoChart Or oShp.Type = msoPicture Then
          ChartTop = oShp.Top: ChartLeft = oShp.Left: ChartWidth = oShp.Width: ChartHeight = oShp.Height
          ' Delete the old chart from the slide
          oShp.Delete
          GoTo CopyChart
        End If
      Next

      ' Copy and Paste a picture of the chart from Excel to PowerPoint
CopyChart:
      oWS.ChartObjects(counter).Chart.CopyPicture
      DoEvents
      Set oShp = oSld.Shapes.Paste
      ' Turn off aspect ratio lock to allow stretched resize
      oShp.LockAspectRatio = msoFalse
      ' Resize and position the chart
      oShp.Top = ChartTop: oShp.Left = ChartLeft: oShp.Width = ChartWidth: oShp.Height = ChartHeight
    Next
    
    ' Optionally minimise PowerPoint window and reactivate Excel window
    'oPPT.WindowState = ppWindowMinimized
#If Mac Then
    ' Do nothing
#Else
    AppActivate Windows(1).Caption
#End If
    
    ' Clear PowerPoint objects
    Set oPPT = Nothing: Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
    ' Clear Excel objects
    Set oWB = Nothing: Set oWS = Nothing
    
    MsgBox "PowerPoint presentation updated.", vbInformation + vbOK, "Updated presentation is available."

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AckeemKAuthor Commented:
Jamie,

Thanks again for all your help! This code accomplishes what I've been trying to do for some time now and I really do appreciate all your help on this.

Thanks,
Ackeem
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
You're welcome Ackeem. Glad it's working for you and thank you for the question!
0
AckeemKAuthor Commented:
One last thing Jamie. I've placed that macro in the "Create Presentation" button and it works perfectly. The only issue is that when I try to rename the file to be "V2" at the end since this is the new version I want to release, I am receiving errors and the file completely shuts down. Any idea if changing the name to read V2 and not V1 affected the smoothness of the macro? I attached it so you can view the tool as well and make the change and see the errors.
DSE-Carelog-Report-V1.xlsm
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
The Excel filename isn't a dependency so I'm not sure what's happening there but I did forget to mention that when I opened your original project, I found a module containing the Sub CreatePresentation() procedure so added my code in that module. The code I have pasted back didn't change that but you need to make sure there aren't now two copies of that procedure in your project now. I'll download your latest file anyway and double check.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Hi Ackeem. I tried downloading your latest Excel file, saving it as "V2", renaming the pptx to your new name "DSE Carelog Slidedeck Business Review Template.pptx" and it works without code changes for me. Note that the Excel file and PowerPoint file must be in the same folder otherwise you will get an error. What error did you get?

By the way, I meant to say earlier, I would recommend renaming the modules from Module1...13 to something more descriptive to help maintain this project. In my test environment, I used "M_PowerPoint", "M_MacPC", "M_SelectFile" etc.
0
AckeemKAuthor Commented:
Hi Jamie. I was just able to fix it by separating out the V2 file and the slide deck into a different folder. I think it is good to go now and I really appreciate your help again! You rock!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.