Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Relative Links in Excel... | Excel Discussion (Misc queries) | |||
Reoccurring Edit Links Error | Excel Discussion (Misc queries) | |||
can't update links...can't find links | Excel Discussion (Misc queries) | |||
Relative paths to external data? | Excel Discussion (Misc queries) | |||
Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) |