Microsoft Office Forums Batch Edit Links

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2012, 07:09 AM
tosti tosti is offline Batch Edit Links Windows Vista Batch Edit Links Office 2007
Novice
Batch Edit Links
 
Join Date: Jan 2012
Posts: 3
tosti is on a distinguished road
Default Batch Edit Links

Hello,

I have created a powerpoint presentation that links from multiple sheets within one excel workbook.



The issue is I need to update the ppt. based on selecting different excel workbooks (which have the same worksheets). I am able to change the source files individually under the "edit links to file" option.

How can I change the source in powerpoint to schange all the sliLE to another excel workbook at once? Is there a vba code that can do this?

Any advise will be much appreciated.

Thanks
Reply With Quote
  #2  
Old 01-31-2012, 07:26 AM
JohnWilson JohnWilson is offline Batch Edit Links Windows 7 64bit Batch Edit Links Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,791
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

If you post the old and new paths it could be possible
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #3  
Old 01-31-2012, 10:18 AM
tosti tosti is offline Batch Edit Links Windows Vista Batch Edit Links Office 2007
Novice
Batch Edit Links
 
Join Date: Jan 2012
Posts: 3
tosti is on a distinguished road
Default

Hi John,

The ppt contains graphs and excel tables.

Original link : C:\MonthEnd\2011Sept.xls

New Path : Original link : C:\MonthEnd\2011Oct.xls
Reply With Quote
  #4  
Old 01-31-2012, 10:33 AM
JohnWilson JohnWilson is offline Batch Edit Links Windows 7 64bit Batch Edit Links Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,791
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

If everything stays the same except the month


Sub fixLinks()
Dim osld As Slide, oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
oshp.LinkFormat.SourceFullName = Replace(oshp.LinkFormat.SourceFullName, "Sept", "Oct")
End If
End If
Next: Next
End Sub

NOTE the new link location must actually exist or it will crash
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #5  
Old 01-31-2012, 10:53 AM
tosti tosti is offline Batch Edit Links Windows Vista Batch Edit Links Office 2007
Novice
Batch Edit Links
 
Join Date: Jan 2012
Posts: 3
tosti is on a distinguished road
Default

Thanks that works but updating all the links however is it possible :
1. For the user to define the path (i.e select the file from a prompt screen as the file link changes on a regular basis).
2. Update the links once the marco is run. (currently i have to manully click on "Update Now" button)
Reply With Quote
  #6  
Old 01-31-2012, 12:51 PM
JohnWilson JohnWilson is offline Batch Edit Links Windows 7 64bit Batch Edit Links Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,791
JohnWilson will become famous soon enoughJohnWilson will become famous soon enough
Default

You should be able to work on this I think.

Sub fixLinks()
Dim osld As Slide, oshp As Shape
Dim strpath As String
strpath = InputBox("Enter the new path", "Edit Path", getexisting(ActivePresentation))
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
oshp.LinkFormat.SourceFullName = strpath
oshp.LinkFormat.Update
End If
End If
Next: Next
End Sub

Function getexisting(opres As Presentation) As String
'this finds the first old link as a prompt
Dim osld As Slide, oshp As Shape
For Each osld In opres.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
If oshp.OLEFormat.ProgID Like "*Excel*" Then
getexisting = oshp.LinkFormat.SourceFullName
Exit Function

If your need are more than this and you cannot edit yourself then as this is obviously a commercial enterprize I'm afraid you will have to hire me!
__________________
Microsoft PowerPoint MVP
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Batch adding text to email subject lines in Outlook paulkaye Outlook 0 12-05-2011 06:38 AM
Batch Edit Links Batch run "inspect documents" ghumdinger Word VBA 3 07-21-2011 04:53 PM
MarkAsFinal - 'Edit Anyway' edit popup issue GovindRS PowerPoint 1 06-20-2011 06:43 AM
Changing the slide master for a batch of presentations amac2003 PowerPoint 0 05-24-2010 11:05 AM
Word 2007...Batch Conversion Wizard ??? mw4man Word 0 12-18-2008 04:38 PM


All times are GMT -7. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft