UPdate PPT Links from excel

[mike31z]

Backgroung Excel 2000 sp3, Powerpoint 2000 sp3.
I have a powerpont presentation with 8 slide 7 of them are linked to 7 worksheets in the same excel file Named "XLS2ppt.xls"
When the data is changed in the xls2ppt.xls
I would like to create a macro to update the links in powerpoint from excel
rather then goint to Powerpoint EDIT|Links| then in popup window selecting each link individual to update.

Mike in Wisconsin

[TrippyTom]

I'd like to know a solution for this as well.

[TrippyTom]

To be more specific, I need the ability to update links while keeping the ranges in tact. The only thing that changed is the filename, but the links to ranges and charts are the same.

The following code just cuts off the ranges completely.

Sub testLinkUpdating()
Dim i As Long
Dim j As Long
Dim sCount As Integer
For i = 1 To ActivePresentation.Slides.Count
For j = 1 To ActivePresentation.Slides(i).Shapes.Count
If ActivePresentation.Slides(i).Shapes(j).Type = msoLinkedOLEObject Then
With ActivePresentation.Slides(i).Shapes(j).LinkFormat
.SourceFullName = "I:\TableB.xls"
.AutoUpdate = ppUpdateOptionAutomatic
.Update
End With
End If
Next j
Next i
End Sub

[mike31z]

I found a fix to this problem by using an ADDIN that was created by Shyam Pillai who submitted a number of fixes to MVPS.org I tried this fix but the side effect are worse then the manually updating the slides during the slide show. Go to MVPS.org and search for updatelinks. That will lead you to the free powerpoint addin.

Here are the side effects that I did not like.
1. The excell file has to be closed. ( this doesn't work for me)
2. If the source excel file had macro you have to mouse click enable macro.
3. If the source excel file worksheet had links you have to mouse click Update links.
4. If you have more then one slide you have to repeate step 2 and 3 for each slide.

In my mind this cure is worse then the problem.

mike in wisconsin.

[mike31z]

I found this and Jon Peltier's help me with a different excel to power point problem a couple of years ago.

http://www.pptfaq.com/FAQ00368.htm

I hope this helps.

I think this question should have been in the Excel portion since more VBA is written there then in powerpoint.

mike in wisconsin

[mike31z]

I found a reference to this problem. Its the 4th Item on the page. The written instructions are not clear to me. My knowledge of VBA to modify the code is not good enough.

http://skp.mvps.org/ppt00040.htm#4

[mike31z]

Situation: Slide presentation is running in a loop on the second monitor(kiosk). I can update the source xls file but in order to update the looping slide show I must stop the slide show and up date one of two ways.
:banghead:
1. Close and reopen the slide file. (easyest less mouse click)

2. Mouse click EDIT, LINKS Then in the popup box Click on each sssource listed the cclicck on the Update Buton. Repeate this step for each excel source.:banghead: :banghead: :banghead:

I could buy some application that can do more cost to much.



Mike in wisconsin

[heriss]

Hi,

I had the same pb with the mouse click issue. However if you open first the linked document it doesn't bother you with any popup for refresh.

Sub BatchEditLink()

Dim oSld As Slide
Dim oShp As Shape
Dim oCmdButton As CommandBarButton
Dim sLinkSource As String, sOriginalLinkSource As String
Dim Link As Variant

Dim OriginalLinkCollection As New Collection, LinkCollection As New Collection
Dim i As Integer


i = MsgBox("To avoid Macro Security prompts, please open the source document before running this macro. Do you want to continue?", vbYesNo)
If i <> 6 Then GoTo CleanExit


Set oCmdButton = CommandBars("Standard").Controls.Add(Id:=2956)
ActiveWindow.ViewType = ppViewSlide
i = 1
'Parse the document to list links to external files

For Each oSld In ActivePresentation.Slides
For Each oShp In oSld.Shapes
If oShp.Type = msoLinkedOLEObject Then
ActiveWindow.View.GotoSlide oSld.SlideIndex
oShp.Select

sOriginalLinkSource = oShp.LinkFormat.SourceFullName
sOriginalLinkSource = Trim(Left(sOriginalLinkSource, InStr(sOriginalLinkSource, "!") - 1))

On Error Resume Next
OriginalLinkCollection.Add sOriginalLinkSource, sOriginalLinkSource 'Name and Key

End If
Next oShp
Next oSld

'Display the file path and prompt for modification
For Each Link In OriginalLinkCollection

'Need to activate the Excel Library
sLinkSource = Excel.Application.GetOpenFileName("Classeurs Excel,*.xls", , "Current File: " & Link)
'Returns 0 if the operation is cancelled
'Set new file path when available
If sLinkSource <> Link And sLinkSource <> "False" Then LinkCollection.Add sLinkSource Else LinkCollection.Add Link
Next


'Parse the document to replace the file path when necessary

For Each oSld In ActivePresentation.Slides
For Each oShp In oSld.Shapes
If oShp.Type = msoLinkedOLEObject Then
ActiveWindow.View.GotoSlide oSld.SlideIndex
oShp.Select

sOriginalLinkSource = oShp.LinkFormat.SourceFullName
sOriginalLinkSource = Left(sOriginalLinkSource, InStr(sOriginalLinkSource, "!") - 1)

i = 0
For Each Link In OriginalLinkCollection
i = i + 1
If sOriginalLinkSource = Link Then
sLinkSource = Replace(oShp.LinkFormat.SourceFullName, Link, LinkCollection(i))

oShp.LinkFormat.SourceFullName = sLinkSource
oShp.LinkFormat.Update

End If
Next

End If
Next oShp
Next oSld

oCmdButton.Delete

CleanExit:

ActiveWindow.View.GotoSlide Index:=1

End Sub

[mike31z]

You went overmy head with this code were did you place the code.

mike in Wisconsin

[heriss]

Hi Mike,

Here are the macros I have in my PowerPoint file. I guess the VBA can be easily adapted to be run from the Excel File should you need to.
As you'll see I've been using some bits of code I got on the Net (thks Shyam) to do what I wanted.

If you simply need to refresh the links the "RefreshAllLinks" macro should be sufficient.
If you've moved your source file to another folder and need to re-establish the links first before refreshing the powerpoint use the "BatchEditLink" macro.

(keep in mind that you might have to open the .xls source file before running the macro if you want to avoid the security prompts)

I hope these will help you figure out how to solve your issue.

Heriss


P.S: I've just noticed that my batchEditLink macro was somehow duplicating a source that was already available. (however mine will let the user select the new path from a explorer window)
--.pptfaq.com/FAQ00759.htm-- . I simply wish I had found the source before doing mine.



Sub EditSingleLink()
' ---------------------------------------------------------------------
' Copyright ?1999-2007, Shyam Pillai, All Rights Reserved.
' ---------------------------------------------------------------------
Dim sLinkSource As String
Dim sOriginalLinkSource As String
Dim i As Integer

On Error Resume Next

i = MsgBox("To avoid Macro Security prompts, please open the source document before running this macro. Do you want to continue?", vbYesNo)
If i <> 6 Then GoTo CleanExit


If ActiveWindow.Selection.ShapeRange.Count <> 1 Then
MsgBox ("Please select one and only one shape, then try again.")
Exit Sub
End If

With ActiveWindow.Selection.ShapeRange(1)
'MsgBox .LinkFormat.SourceFullName
sOriginalLinkSource = .LinkFormat.SourceFullName
sLinkSource = InputBox("Edit the link", "Link Editor", sOriginalLinkSource)

If sLinkSource = sOriginalLinkSource Then
' nothing changed; our work on this planet is done
Exit Sub
End If
If sLinkSource = "" Then
' The user canceled; quit:
Exit Sub
End If

' Get the filename portion of the link in case it's a link to a range
Debug.Print Mid$(sLinkSource, 1, InStr(sLinkSource, ".") + 3)

' Is it a valid filename? Is the file where it belongs?
' Test against the filename portion of the link in case the link includes
' range information
If Dir$(Mid$(sLinkSource, 1, InStr(sLinkSource, ".") + 3)) <> "" Then
.LinkFormat.SourceFullName = sLinkSource
.LinkFormat.Update
Else
MsgBox "Can't find " & sLinkSource
End If

End With

CleanExit:

ActiveWindow.view.GotoSlide Index:=1
SetSlideTab

End Sub


Sub SetSlideTab()
' ---------------------------------------------------------------------
' Copyright ?1999-2007, Shyam Pillai, All Rights Reserved.
' ---------------------------------------------------------------------
Dim oCmdButton As CommandBarButton
Set oCmdButton = CommandBars("Standard").Controls.Add(Id:=6015)
DoEvents
ActiveWindow.ViewType = ppViewNormal

If Not oCmdButton Is Nothing Then
If ActiveWindow.Panes(1).ViewType = ppViewOutline Then
oCmdButton.Execute
End If
oCmdButton.Delete
Set oCmdButton = Nothing
End If
End Sub

Sub SetOutlineTab()
' ---------------------------------------------------------------------
' Copyright ?1999-2007, Shyam Pillai, All Rights Reserved.
' ---------------------------------------------------------------------
Dim oCmdButton As CommandBarButton
Set oCmdButton = CommandBars("Standard").Controls.Add(Id:=6015)
DoEvents
ActiveWindow.ViewType = ppViewNormal

If Not oCmdButton Is Nothing Then
If ActiveWindow.Panes(1).ViewType = ppViewThumbnails Then
oCmdButton.Execute
End If
oCmdButton.Delete
Set oCmdButton = Nothing
End If
End Sub




Sub RefreshAllLinks()
Dim oSld As Slide
Dim oShp As Shape
Dim oCmdButton As CommandBarButton
Set oCmdButton = CommandBars("Standard").Controls.Add(Id:=2956)
ActiveWindow.ViewType = ppViewSlide
For Each oSld In ActivePresentation.Slides
For Each oShp In oSld.Shapes
If oShp.Type = msoLinkedOLEObject Then
ActiveWindow.view.GotoSlide oSld.SlideIndex
oShp.Select
oShp.LinkFormat.Update
End If
Next oShp
Next oSld
oCmdButton.Delete
ActiveWindow.view.GotoSlide Index:=1
SetSlideTab
End Sub



Sub BatchEditLink()

Dim oSld As Slide
Dim oShp As Shape
Dim oCmdButton As CommandBarButton
Dim sLinkSource As String, sOriginalLinkSource As String
Dim Link As Variant

Dim OriginalLinkCollection As New Collection, LinkCollection As New Collection
Dim i As Integer


i = MsgBox("To avoid Macro Security prompts, please open the source document before running this macro. Do you want to continue?", vbYesNo)
If i <> 6 Then GoTo CleanExit


Set oCmdButton = CommandBars("Standard").Controls.Add(Id:=2956)
ActiveWindow.ViewType = ppViewSlide
i = 1
'Parse the document to list links to external files

For Each oSld In ActivePresentation.Slides
For Each oShp In oSld.Shapes
If oShp.Type = msoLinkedOLEObject Then
ActiveWindow.view.GotoSlide oSld.SlideIndex
oShp.Select

sOriginalLinkSource = oShp.LinkFormat.SourceFullName
sOriginalLinkSource = Trim(Left(sOriginalLinkSource, InStr(sOriginalLinkSource, "!") - 1))

On Error Resume Next
OriginalLinkCollection.Add sOriginalLinkSource, sOriginalLinkSource 'Name and Key

End If
Next oShp
Next oSld

'Display the file path and prompt for modification
For Each Link In OriginalLinkCollection

'Need to activate the Excel Library
sLinkSource = Excel.Application.GetOpenFileName("Classeurs Excel,*.xls", , "Current File: " & Link)
'Returns 0 if the operation is cancelled
'Set new file path when available
If sLinkSource <> Link And sLinkSource <> "False" Then LinkCollection.Add sLinkSource Else LinkCollection.Add Link, Link
Next


'Parse the document to replace the file path when necessary

For Each oSld In ActivePresentation.Slides
For Each oShp In oSld.Shapes
If oShp.Type = msoLinkedOLEObject Then
ActiveWindow.view.GotoSlide oSld.SlideIndex
oShp.Select

sOriginalLinkSource = oShp.LinkFormat.SourceFullName
sOriginalLinkSource = Left(sOriginalLinkSource, InStr(sOriginalLinkSource, "!") - 1)

i = 0
For Each Link In OriginalLinkCollection
i = i + 1
If sOriginalLinkSource = Link Then
sLinkSource = Replace(oShp.LinkFormat.SourceFullName, Link, LinkCollection(i))

oShp.LinkFormat.SourceFullName = sLinkSource
oShp.LinkFormat.Update

End If
Next

End If
Next oShp
Next oSld

oCmdButton.Delete

CleanExit:

ActiveWindow.view.GotoSlide Index:=1
SetSlideTab
End Sub