How can I change 50 links QUICKLY from a SpSht source to a Word D.

W

Wheelwater

I have a word document with 50 links in it from a spreadsheet. I use the file
daily.

I need to change the speadsheet name and the word document name, while
keeping the links between them active. Then, I need to enter new information
in the original spreadsheet with the original word document.

The problem is that it takes too long to change 50 links each time and even
if I do, it acts unreliably as though still linked to the original.

Any help greatly appreciated...how can I do a fifty link change quickly???
 
C

Chuck

Could you be more specific about what you mean when you say "link"? Do you
mean an OLE link or a hyperlink? Do you have any code already?
 
W

wheelwater

Chuck said:
Could you be more specific about what you mean when you say "link"? Do you
mean an OLE link or a hyperlink? Do you have any code already?
Click to expand...


Multiple sheet Spreadsheet has been ( from up to 12 different sheets)
linked to a 9 page word document (on various sheets) by "copying" the
cell(s) in The SpSh and then "pasting special" onto the word document either
as an MS Object or as formatted text. However, would like to be able to solve
problem for any link, i.e. picture, unformatted, etc.

Wheelwater
 
C

Chuck

Unfortunately from what you say the objects aren't linked to anything because
you copied/pasted them instead of inserting them as linked objects either
manually - Insert>Create from file>Link to file - or programmatically -
ActiveDocument.Shapes.AddOLEObject. There isn't any way to update the links
because there aren't any links to update.

You'll need to replace the current OLE objects with linked OLE objects.
Then you should be able to update them as follows. For more info check the
VBE help on LinkFormat and SourceName, SourcePath and SourceFullName.

Dim shpOLE As InlineShape

For Each shpOLE In ActiveDocument.InlineShapes
With shpOLE
If .Type = 2 Then
With .LinkFormat
.SourceFullName = "c:\test\oletest.xls"
.AutoUpdate = True
End With
End If
End With
Next shpOLE
 
W

wheelwater

Chuck said:
Unfortunately from what you say the objects aren't linked to anything because
you copied/pasted them instead of inserting them as linked objects either
manually - Insert>Create from file>Link to file - or programmatically -
ActiveDocument.Shapes.AddOLEObject. There isn't any way to update the links
because there aren't any links to update.

You'll need to replace the current OLE objects with linked OLE objects.
Then you should be able to update them as follows. For more info check the
VBE help on LinkFormat and SourceName, SourcePath and SourceFullName.

Dim shpOLE As InlineShape

For Each shpOLE In ActiveDocument.InlineShapes
With shpOLE
If .Type = 2 Then
With .LinkFormat
.SourceFullName = "c:\test\oletest.xls"
.AutoUpdate = True
End With
End If
End With
Next shpOLE
Click to expand...
Thanks Chuck, but the "paste special" function does create a linked object.
Try it you'll like It! Yican create an object, unformatted text and ormatted
text as well as a hyperlink and more. Copy the item from the source
spreadsheet, move to the document, select "Edit", "Paste special" , mark the
link box and select the format type.

Tom
 
C

Chuck

Sorry, you're absolutely right. I missed that detail. In that case the
following code should work (obviously SourceFullName should be changed as
required):

Dim shpOLE As Shape

For Each shpOLE In ActiveDocument.Shapes
With shpOLE
If .Type = 10 Then
With .LinkFormat
.SourceFullName = "c:\test\oletest2.xls"
.Update
End With
End If
End With
Next shpOLE
 
C

Chuck

PS - you'll explore the ways different Shape.Type values affect the code
depending on whether you're updating a linked picture, file, whatever. That
If test may not be necessary but I don't have the time to test all the
different possibilities... HTH
 
Top