Excel Macro to Open Powerpoint presentation and automatically update links.

[cwhite1998]

I have an extensive excel spreadsheet that I have designed that requires user input from excel userforms to generate raw data for a detailed analysis. My goal is to export this data to a powerpoint presentation that I have designed that puts the data into a readable form and expains it simply and clearly.

Currently, I have used copy / paste special / paste link in order to link each excel cell to my powerpoint presentation.

Now comes the sticky part... I need to program a macro within excel to open my powerpoint presentation and automatically update the links. So far, I have managed to get the presentation to open, but I can't figure out how to update the links.

I am very new to VBA programming, so any help would be greatly appreciated. Also, if you have a better way to get this data over to my presentation, Im very open to suggestions.

My code so far is as follows:
Sub GotoSlide()

Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim sPreso As String

sPreso = "E:\Documents and Settings\My Documents\MyPresentation.ppt"


On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")
If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
End If
On Error GoTo 0
On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)
If Err.Number <> 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
End If
On Error GoTo 0

With PowerPoint.ActivePresentation


End Sub

[geekgirlau]

[VBA]Sub GotoSlide()
Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim sPreso As String

sPreso = "E:\Documents and Settings\My Documents\MyPresentation.ppt"

On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")

If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
End If

On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)

If Err.Number <> 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
End If

On Error GoTo 0
pPreso.UpdateLinks
End Sub[/VBA]

[cwhite1998]

Thanks for the response.
I tried your code, but it returns a Runtime Error 91 (Object variable with block variable not set) on the line pPreso.update links. Any idea how to fix this?

[geekgirlau]

Are you sure there are links in the presentation? If you open it manually, does it ask you if you want to refresh the links?

[cwhite1998]

Ok. I looked at it again and realized that I had the file in the wrong directory. Everything works great now. THanks for you help!!!