How can I programmatically relink OLE's in a Word document?

[Oli]

Thank you, Jean-Guy! Believe it or not I was able to change the formulas
(which are links) with the "Replace" Method in embedded excel objects. The
macro worked in a short sample document I created. The formula in the first
cell of the object, for example is: A1 = ='C:\Documents and
Settings\Owner\Desktop\[trial1.xls]Table 2'!A1. I asked in the macro:
Replace "C:\Documents and Settings\Owner\Desktop\[trial1.xls]" with
"C:\Documents and Settings\Owner\Desktop\[trial2.xls]" And, it worked! When I
returned back to my original monster document though, I faced with another
problem. I saw that one of the sheets called "Table10" does not exist in the
replacement file. If I can make the macro to skip to replace when the sheet
doesn't exist or ideallly to put zeros instead, I will have the solution. Do
you think I can do that? Here is what I did (and appears to be working if
the original and the replacement workbooks have the same number of worksheets
and the worksheet names are the same):

Sub ChangeObject()

Dim k As Long
Dim objEXL As Object
Dim boolExcel As Boolean

boolExcel = False

With ActiveDocument
' Loop through all the floating shapes in document.
For k = 1 To .Shapes.Count
With .Shapes(k)
' If the shape's type is an OLE object then...
If .Type = msoEmbeddedOLEObject Then
If .OLEFormat.ProgID = "Excel.Sheet.8" Then
boolExcel = True
.OLEFormat.Activate
Set objEXL = .OLEFormat.Object
With objEXL.ActiveSheet
.Cells.Replace What:="C:\Documents and
Settings\Owner\Desktop\[trial1.xls]", _
Replacement:="C:\Documents and
Settings\Owner\Desktop\[trial3.xls]", LookAt:=xlPart, MatchCase:=False
.Cells.Replace What:="C:\Documents and
Settings\Owner\Desktop\[trial2.xls]", _
Replacement:="C:\Documents and
Settings\Owner\Desktop\[trial4.xls]", LookAt:=xlPart, MatchCase:=False
End With
End If
End If
End With
Next k
End With

If boolExcel Then
SendKeys "{ESC}"
End If

End Sub

 

[Oli]

Thank you, Doug! After I tried your code, I saw that it changes and updates
the field links in the document. I will definitely utilize it when I have
field links in my document. I was able to modify Jean-Guy's code with the
Replace Method, and change the formulas (links) in the embedded objects, too.
Thank you, again!!!

 

[Oli]

Jean-Guy, I solved my problem by adding the missing worksheet in the
replacement workbook. Inserting the "Replace Method" in your sample code
accomplished what I needed! Of course, if you have an answer for the
question below, I would love to hear it.

I would like to thank you for responding ALL of my questions in the past ten
days. You have been EXTREMELY helpful. I couldn't have completed this task
without your help. THANK YOU VERY MUCH!!!

Thank you, Jean-Guy! Believe it or not I was able to change the formulas
(which are links) with the "Replace" Method in embedded excel objects. The
macro worked in a short sample document I created. The formula in the first
cell of the object, for example is: A1 = ='C:\Documents and
Settings\Owner\Desktop\[trial1.xls]Table 2'!A1. I asked in the macro:
Replace "C:\Documents and Settings\Owner\Desktop\[trial1.xls]" with
"C:\Documents and Settings\Owner\Desktop\[trial2.xls]" And, it worked! When I
returned back to my original monster document though, I faced with another
problem. I saw that one of the sheets called "Table10" does not exist in the
replacement file. If I can make the macro to skip to replace when the sheet
doesn't exist or ideallly to put zeros instead, I will have the solution. Do
you think I can do that? Here is what I did (and appears to be working if
the original and the replacement workbooks have the same number of worksheets
and the worksheet names are the same):

Sub ChangeObject()

Dim k As Long
Dim objEXL As Object
Dim boolExcel As Boolean

boolExcel = False

With ActiveDocument
' Loop through all the floating shapes in document.
For k = 1 To .Shapes.Count
With .Shapes(k)
' If the shape's type is an OLE object then...
If .Type = msoEmbeddedOLEObject Then
If .OLEFormat.ProgID = "Excel.Sheet.8" Then
boolExcel = True
.OLEFormat.Activate
Set objEXL = .OLEFormat.Object
With objEXL.ActiveSheet
.Cells.Replace What:="C:\Documents and
Settings\Owner\Desktop\[trial1.xls]", _
Replacement:="C:\Documents and
Settings\Owner\Desktop\[trial3.xls]", LookAt:=xlPart, MatchCase:=False
.Cells.Replace What:="C:\Documents and
Settings\Owner\Desktop\[trial2.xls]", _
Replacement:="C:\Documents and
Settings\Owner\Desktop\[trial4.xls]", LookAt:=xlPart, MatchCase:=False
End With
End If
End If
End With
Next k
End With

If boolExcel Then
SendKeys "{ESC}"
End If

End Sub

------------

Oli was telling us:
Oli nous racontait que :


As I wrote before you cannot link an embedded object to an external source.
The object is either linked or embedded, it cannot be both.
So if you want to "link" your embedded objects, you will have to remove
them, and then paste the Excel date as links (Edit > Paste Special).

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Oli was telling us:
Oli nous racontait que :

Thank you, Jean-Guy! Believe it or not I was able to change the
formulas (which are links) with the "Replace" Method in embedded
excel objects. The macro worked in a short sample document I
created. The formula in the first cell of the object, for example is:
A1 = ='C:\Documents and Settings\Owner\Desktop\[trial1.xls]Table
2'!A1. I asked in the macro: Replace "C:\Documents and
Settings\Owner\Desktop\[trial1.xls]" with "C:\Documents and
Settings\Owner\Desktop\[trial2.xls]" And, it worked! When I returned
back to my original monster document though, I faced with another
problem. I saw that one of the sheets called "Table10" does not
exist in the replacement file. If I can make the macro to skip to
replace when the sheet doesn't exist or ideallly to put zeros
instead, I will have the solution. Do you think I can do that? Here
is what I did (and appears to be working if the original and the
replacement workbooks have the same number of worksheets and the
worksheet names are the same):

I am glad that you got it working.

Next time, as a hint, it might be helpful if you painted a picture of what
you actually did to get the document content you are working with.

Now, after the fact, I have just finally understood that you wanted to link
*cells* in an embedded object to cells in another Excel file, and not relink
the Excel object itself.

Good luck.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org