Macro Coding to Open PowerPoint in Excel

Question

Hello, I am trying to find a way to update a PowerPoint based on things in an Excel Spreadsheet. I wanted to do things like update graphs by pulling data from other spreadsheets (which I able to do) but then in powerpoint update all the graphs and continue to cut copy paste a picture that is in excel into a specific PowerPoint slide (or replace the picture with another picture that has a consistent filename)

Currently all I have is a broken code to open a powerpoint file:

Sub OpenPPT()

Dim PowerPointApp As PowerPoint.Application

Dim myPresentation As PowerPoint.Presentation

Set PowerPointApp = GetObject(, "PowerPoint.Application")

Set myPresentation = PowerPointApp.ActivePresentation

PowerPointApp.Presentations.Open ("C:\Users\T0227996\Desktop\Christian\Dashboard Sample.pptx")

***Run-time error '-2147024894 (800700002)'

***Method 'Open' of object 'Presentations' failed

End Sub

Please let me know if you have any suggestions as to how to solve this problem as well as how to proceed with the current vision described in my first paragraph. Thank you.

Answer 1

Before you deal with the security problems I suggest that you open the presentation manually.

Then try the code below.

Have also a look at this thread:

http://answers.microsoft.com/en-us/office/forum...

Andreas.

Sub UpdateChart()
  Dim oPPTApp As Object 'PowerPoint.Application
  Dim oPPTShape As Object 'PowerPoint.Shape
  Dim oGraph As Object 'PowerPoint.Chart

  Dim rngNewRange As Excel.Range

  'Try access to Powerpoint
  On Error Resume Next
  Set oPPTApp = GetObject(, "PowerPoint.Application")
  On Error GoTo 0
  If oPPTApp Is Nothing Then
    MsgBox "Open your presentation first."
    Exit Sub
  End If

  'Get current data in this sheet
  Set rngNewRange = Range("A1").CurrentRegion
  rngNewRange.Copy

  With oPPTApp.ActivePresentation.Slides(1)
    'Visit each Shape on Slide 1
    For Each oPPTShape In .Shapes
      'Check to see whether shape is an OLE object.
      Select Case oPPTShape.Type
        Case msoEmbeddedOLEObject
          'Check to see whether OLE object is a Graph object.
          If InStr(1, oPPTShape.OLEFormat.ProgId, "MSGraph.Chart", vbTextCompare) > 0 Then
            'Set oGraph to the Chart object on the slide.
            Set oGraph = oPPTShape.OLEFormat.Object
            'Paste the cell range into the datasheet of that chart
            oGraph.Parent.DataSheet.Range("A1").Paste True
          End If
        Case msoChart
          'Get the Chart object
          Set oGraph = oPPTShape.Chart
          'Link to the data in Excel
          oGraph.SetSourceData rngNewRange.Address(External:=True)
      End Select
    Next
  End With
End Sub

Answer 2

The short answers are: No and Yes. :-)

A programmer, like me, will never record a macro, unless you want to see which objects are being referred.

After that you have the Intellisense in VBA, resp. the Object catalog (press F2 inside the VBA editor) and can view and search which properties or method are available for each object (class).

And If you found something that looks like what you're looking for, you can read the documentation for more details

https://docs.microsoft.com/en-us/office/vba/api...

To know how the debugger works is also a good step, here is a tutorial

http://www.wiseowl.co.uk/blog/s161/online-excel...

Andreas.

Answer 3

can you record this macros? OR does everything have to be done manually?