excel to ppt VBA not working

[Guest]

I’m been reading and gathering codes in this forum to achieve what I want.
But it does not work. My objective is simple. I have some simple automation
in excel and want to integrate powerpoint to complete my process. At the end
of my data run there is a master excel template. Btw, I did setup the
references in VBA.

Here is what I need to do: A powerpoint master file links in the data/charts
from excel template. I will break link in ppt and save it as paste value
version.

I hit an error (run time error 438) at “Set oSlides =
ActiveWindow.Presentation.Slides†.

Hope someone can help to correct this VBA.

Sub test_link()
'
Dim oPPTApp As PowerPoint.Application
Dim oPPTPres As PowerPoint.Presentation
Dim sPresentationFile As String

Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange

sPresentationFile = "C:\Desktop\testMacro.PPT"

Set oPPTApp = New PowerPoint.Application
oPPTApp.Visible = True

‘ This is from Sub UngroupTheOLEs(). Suppose to break links.
Set oSlides = ActiveWindow.Presentation.Slides
For Each oSld In oSlides
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoEmbeddedOLEObject Or _
oShp.Type = msoLinkedOLEObject Then
Set oShapeRange = oShp.Ungroup
oShapeRange.Group
End If
Next oShp
Next oSld

' Save as PV version and close presentation
With PPPres
.SaveAs "C:\testMacro_PV.PPT"
.Close
End With


' Cleanup
' Close the presentation
oPPTPres.Close
' Quit PPT
oPPTApp.Quit
' Release variables
Set oPPTPres = Nothing
Set oPPTApp = Nothing

End Sub

 

[Bob Phillips]

You have to open the presentation, and then reference activepresentation.
ACtivewindow is the Excel window.

Set oPPTApp = New PowerPoint.Application
oPPTApp.Visible = True
oPPTApp.Presentations.Open sPresentationFile

' This is from Sub UngroupTheOLEs(). Suppose to break links.
Set oSlides = oPPTApp.ActivePresentation.Slides
For Each oSld In oSlides
....

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob. I can see the presentation being opened. I still hit the same
error msg ( "run time error 438" at "Set oSlides =
ActiveWindow.Presentation.Slides" ). Since the ppt is linked to excel, could
it be expecting acknowledgement to update links? How do I get around this?

Eddy

 

[Bob Phillips]

That is because that line should have been deleted, read what I said again.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I was too excited. I've clean up that part of the code. I then encountered a
different error ('13', type mismatch) at "Set oShapes = oSld.Shapes".

I'm really not proficient, as you can tell, in this. Appreciate your
patience and help rendered. I'll just need to break links and save it as
another file name.


Sub test_link()
'
Dim oPPTApp As PowerPoint.Application
Dim oPPTPres As PowerPoint.Presentation
Dim sPresentationFile As String

Dim oSlides As Slides
Dim oSld As Slide
Dim oShapes As Shapes
Dim oShp As Shape
Dim oShapeRange As ShapeRange

sPresentationFile = "C:\testmacro.PPT"

Set oPPTApp = New PowerPoint.Application
oPPTApp.Visible = True
oPPTApp.Presentations.Open sPresentationFile

‘ This is from Sub UngroupTheOLEs(). Suppose to break links.
Set oSlides = oPPTApp.ActivePresentation.Slides

For Each oSld In oSlides
Set oShapes = oSld.Shapes
For Each oShp In oShapes
If oShp.Type = msoEmbeddedOLEObject Or _
oShp.Type = msoLinkedOLEObject Then
Set oShapeRange = oShp.Ungroup
oShapeRange.Group
End If
Next oShp
Next oSld

' Save as PV version and close presentation
With PPPres
.SaveAs "C:\testmacro_PV.PPT"
.Close
End With


' Cleanup
' Close the presentation
oPPTPres.Close
' Quit PPT
oPPTApp.Quit
' Release variables
Set oPPTPres = Nothing
Set oPPTApp = Nothing

End Sub