Powerpoint VBA to switch back to powerpoint from Excel

Question

I hope someone can help....

I have a powerpoint presentation, which has linked tables and graphs from an excel file. the updating of the slides are set to manual.

i have created a VBA code in Powerpoint which opens up the excel file. I am trying to update the links in the powerpoint through VBA instead of manually choosing each linked element and updating the values. while the first part of my VBA code works in opening up the excel file, the links are not being updated, which i think is down to not being back in the powerpoint to update the links, so I am trying to include in my VBA code lines which will go back to the powerpoint presentation, after which i assume the the line to update links will work (happy to be corrected). below is the code i have built so far....my comments are in bold ...

any suggestions? FYI, I am using office 2007.

Thanks

Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("File location\filename.xlsm", True, False)

Set xlApp = Nothing
Set xlWorkBook = Nothing

Section above opens the excel file which contains the linked tables and charts

On Error Resume Next
  With GetObject(, "PowerPoint.Application")
    .ActivePresentation.SlideShowWindow.Activate
  End With

Section above i was hoping would go back to the powerpoint after opening the excel file but it does not which is why i think the code below to update links is not working

ActivePresentation.UpdateLinks


End Sub

Answer 1

Start from something easier. This will allow you to activate the first existing PowerPoint application from Excel:

Option Explicit

Public Sub TestMe()

    Dim ppt As New PowerPoint.Application
    ppt.visible = msoTrue
    ppt.Windows(1).Activate

End Sub

Then play a bit with it and fix it into your code.

Answer 2

@Vityata

Ok, i got it to work....original coding did the first part of opening the excel file, and to switch back to powerpoint (and i think this will only work if there is only 1 presentation open i added the following code...

AppActivate "Microsoft PowerPoint"

so my complete code looks like:

Sub test() Dim xlApp As Object Dim xlWorkBook As Object Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlWorkBook = xlApp.Workbooks.Open("file path\file name.xlsm", True, False)

Set xlApp = Nothing Set xlWorkBook = Nothing AppActivate "Microsoft PowerPoint"

End Sub

now to get manual links to update as part of the vba code...

Answer 3

If you capture the file that your macro is in. This is just a string of your path and filename

'This is the macro file
MacroFile = ActivePresentation.FullName

Then you can use that variable to activate just that specific PowerPoint presentation.

Use Presentations(MacroFile).Activate or Presentations(MacroFile).Updatelinks

It's best not to use ActivePresentation when moving between applications.