Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel to ppt VBA not working
Im 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel to ppt VBA not working
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) "Eddy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel to ppt VBA not working
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" wrote: 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) "Eddy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel to ppt VBA not working
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) "Eddy" wrote in message ... 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" wrote: 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) "Eddy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel to ppt VBA not working
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 "Bob Phillips" wrote: 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) "Eddy" wrote in message ... 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" wrote: 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) "Eddy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
why is my excel not working | Excel Discussion (Misc queries) | |||
Working in Excel | Excel Worksheet Functions | |||
working with excel | Excel Worksheet Functions | |||
Working with Excel | Excel Discussion (Misc queries) |