VBA Change Excel links in PPT 97

  • Thread starter Thread starter Excel Question
  • Start date Start date
E

Excel Question

I have a powerpoint book presentation1.ppt with 50 objects pasted as
links connected to worksheets in workbook1.xls.

Every month I duplicate workbook1 and resave it as workbook2.xls
before changing it.

I then duplicate presentation1.ppt and resave it as presentation2.ppt

I would like a fast way to change all the links in presentation2.ppt
from workbook1.xls to their exact same locations in workbook2.xls

I currently go into the edit links dialog box and manually change the
source for each link.

The macro recorder is coming up blank.

It must be possible because I think I've seen software seems to access
the links programmatically.

Can anyone point me in the direction for how to access the links from
vba?
 
Why not avoid macros all together?

You could use the relative linking to swap out the excel file.

Folder A -- March 03
Presentation 1 -- that links relatively to
copy of Workbook 1 (named monthsales.xls)

Folder B -- April 03
Copy of Presentation 1
Copy of Workbook 2 (named monthsales.xls)

Or you could (using similar technique) use a VBA front-end to browse for and
copy the excel spreadsheet from it's source to an exact filename in the
presentation folder. Then you would only need one copy of the presentation
on the harddrive.

Just a few thoughts,
B

--
Please spend a few minutes checking out www.pptfaq.com This link will
answer most of our questions, before you think to ask them.

Change org to com to defuse anti-spam, ant-virus, anti-nuisance
misdirection.
 
Why not avoid macros all together?Thanks for the idea. The names and locations of all the relevant
files are decided outside of my control.

Is there something I missed where VBA allows direct access to the link
locations or does it have to be done through some even more advanced
techniques such as using windows API's?

I guess I could always use sendkeys and pretend vba is a user doing
all the changes manually.

Do any of the VBA guys have any suggestions for where to look?
 
Use the following code:
' ----- Beginning Of Code -----
Option Explicit

Public Const NewLinkPath = "F:\Under Development\Product\"
Public Const NewFilename = "excel2.xls"

Sub UpdateToNewLinks()
Dim I As Integer
Dim J As Integer

' Create a variable to store the file reference string.
Dim LinkFileName As String
Dim LinkRange As String
Dim LinkSource As String
' 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 J = 1 To .Shapes.Count

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

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

' Ideally you should determine the type of OLE Object
before working on it.
If .OLEFormat.ProgID = "Excel.Sheet.8" Then
With .LinkFormat
' Now we shall extract the filename from the
complete path.
LinkSource = Right(.SourceFullName, InStr(1, _

StrReverse(.SourceFullName), "\") - 1)
LinkFileName = Left(LinkSource, InStr(1,
LinkSource, "!") - 1)
LinkRange = Mid(LinkSource, Len(LinkFileName) +
1)

' Concatinate the new Path and filename and
assign it to the SourceFullName

.SourceFullName = NewLinkPath & NewFilename &
LinkRange

.AutoUpdate = ppUpdateOptionAutomatic

End With
End If
End If

End With
Next J
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

Public Function StrReverse(ByVal sIn As String) As String
Dim nC As Integer, sOut As String
For nC = Len(sIn) To 1 Step -1
sOut = sOut & Mid(sIn, nC, 1)
Next
StrReverse = sOut
End Function
' ----- End Of Code -----

--
Regards
Shyam Pillai

Batch Exporter
http://www.mvps.org/skp/products/xport/
 
Back
Top