Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default Setting links to relative paths from ppt to xls

I have a powerpoint linked to parts of 20 speadsheets.
In Office 2000, when I move the files to a different machine, ppt's update links says it can't find the links (a familiar story, except with a powerpoint twist).
(BTW: XP seems to find its way just fine).

I need a ppt macro just like ChangeQuerySources that sets the ppt links to the linked Excel objects to a relative path. I think it's the same code except I don't know the name of the ppt object that would be used instead of QueryTable.

Your help is greatly appreciated.
  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

I am not a PPT expert - indeed I have never looked at linked objects in
PPT before, but I think this might give you a start:

Sub ChangeLinkedObjects(FromDir As String, ToDir As String)
Dim Slid As Slide
Dim Shap As Shape
Dim Link As String
For Each Slid In ActivePresentation.Slides
For Each Shap In Slid.Shapes
On Error GoTo NoLink
Link = Shap.LinkFormat.SourceFullName
On Error GoTo BadChange
If LCase(Left(Link, Len(FromDir))) = LCase(FromDir) Then
Shap.LinkFormat.SourceFullName = ToDir & Mid(Link, Len(FromDir)
+ 1)
End If
NextShape:
Next
Next
Exit Sub
NoLink:
Resume NextShape
BadChange:
MsgBox "Failed to change link:" & Link
Resume NextShape
End Sub

Let us know if this deals with everything.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Junior Member
 
Posts: 2
Default

I cobbled this macro (borrowed from snippets of several others) that works great.

I would prefer a simple OLE patch, but it seems the simplest is a lengthy security patch from 2003.
If anybody has a patch to MS cases 814672 (XL2000) and 812647 (XL2002) please let me know.

Many thanks for your help.

Regards.


Sub InitPptLinks()
'
' Macro created 8/12/2005
'
' Ppt Macro to initialize links to Excel the very first time the ppt is ever openned
' after being moved to new a computer
'
' Once the macro is created in ppt:
' Tools / Macro / Security / Medium or Low
' Save
' Move the xls and ppt to a new machine
' Always keep them together in the same folder
' Open the xls
' Open the ppt
' Click Enable Macros (if prompted)
' When asked to Update Links, click Cancel
' Tools / Macro / Macros / Run (InitPptLinks)
'
' The xls and ppt may now be moved together to any folder
'
' This macro only needs to be run once when files have been moved
' to a new machine or disk names have been changed
'

Dim i As Integer
Dim k As Integer
Dim pos As Integer
Dim CurFolder As String
Dim P As Long, LastP As Long

' Create a variable to store the worksheet reference string.
Dim linkname As String

' Create a variable to store the position of the worksheet
' reference in the .SourceFullName property of the OLEFormat
' object.
Dim linkpos As Integer

' Set a For loop to go from slide 1 to the last slide in the
' presentation.
For i = 1 To ActivePresentation.Slides.Count

' Select the slide based on the index value.
With ActivePresentation.Slides(i)

' Loop through all the objects on slide.
For k = 1 To .Shapes.Count

' Use only shapes on the slide.
With .Shapes(k)

' If the shape's type is an OLE object then...
If .Type = msoLinkedOLEObject Then

' Change the path to new source and set the update
' type to Automatic. First find where the worksheet
' reference is, and then store it in linkname. Next
' assign the new path and concatenate the chart name
' to the end of the path.
With .LinkFormat

' Find where in the source path string the
' character "!" occurs, and assign the position
' to the variable linkpos.
linkpos = InStr(1, .SourceFullName, "!", _
vbTextCompare)

' Get the Current Folder

CurFolder = ActivePresentation.FullName

' Find the last "\" in the path name

P = InStr(1, CurFolder, "\")
Do While P
LastP = P
P = InStr(LastP + 1, CurFolder, "\")
Loop

CurFolder = Mid(CurFolder, 1, LastP)

' Assign linkname to worksheet reference at the
' end of the Current Folder path.

linkname = Right(.SourceFullName, _
Len(.SourceFullName) - linkpos)
.SourceFullName = CurFolder & "your_file_name.xls!" & linkname
.AutoUpdate = ppUpdateOptionAutomatic
End With

End If

End With

Next k

End With

Next i

' Update all links in the presentation, so that the changes are
' visible and the source file locations are correct on the screen.
ActivePresentation.UpdateLinks
End Sub
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Relative Links in Excel... colleenh Excel Discussion (Misc queries) 1 June 28th 05 10:47 PM
Reoccurring Edit Links Error Spyder Excel Discussion (Misc queries) 6 April 12th 05 02:30 PM
can't update links...can't find links GJR3599 Excel Discussion (Misc queries) 1 April 4th 05 04:56 PM
Relative paths to external data? Christopher Blue Excel Discussion (Misc queries) 3 January 1st 05 09:14 PM
Links in formulas change when another user runs a workbook L Mehl Excel Discussion (Misc queries) 2 November 27th 04 10:27 PM


All times are GMT +1. The time now is 07:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"