Oli was telling us:
Oli nous racontait que :
Thank you again, Jean-Guy! And, sorry for the length of my reply
below.
First of all, My MS Word Version is 2002 and I am trying to add the
code in MS Visual Basic 6.3.
Secondly, I realize now that I failed to explain clearly what I am
trying to accomplish. I believe I know why your code is not working
for me: My Word document is linked to TWO different Excel documents,
not one. Moreover, both Excel sheets have 196 different tabs. What
I want to accomplish is this:
By tabs I guess you mean Worksheets, each having a tab at the bottom with
its name.
1) Find the first Excel source: P:\Folder1\FolderA\Book1.xls and
replace it with Q:\Folder3\FolderC\Book3.xls
2) Then find the second Excel source P:Folder2\FolderB\Book2.xls and
replace it with Q:\Folder4\FolderD\Book4.xls
If you click on any embedded Excel object and go to any cell, the
Excel workbook reference will be shown with brackets and we will see
Where will it be shown?
In Word, If I simply click on an embedded linked Excel cell range, it just
gets selected. If I double click on it, it opens the Excel workbook.
I could not see what you are seeing.
the tab name and cell references. For example the first cell of the
embedded sheet will say: A1='P:\Folder1\FolderA\[Book1.xls]Table1'!A2
And, another cell will say: B1=
A1='P:\Folder1\FolderA\[Book1.xls]Table2'!A2
Please note that tab names and cell references are identical in
replacement Excel sheets. Meaning, the linked Excel workbooks are
saved under different directories and different folders with
different names. However, the tab names are the same.
Given my problem, I tried a different solution. I attempted to
change your code as in the following. Naturally, it didn't work. (I
would have fainted if it did.......)
Since you know now what is going on, I hope you can correct the mess
I put below. When I run the following macro, it gives me an error of
"Compile error: Method or data member not found" for the first "If
.SourceFullName" and it stops.
This is because you removed the
With .LinkFormat
line.
Also, there is an error in your variable declaration and variable use (Dim
SrtLink2 As String vs StrLink2)
It is a good habit to use
Option Explicit
at the top of the code. If you go in the VBA options and preferences, you
can get it to add it automatically whenever you create a module.
I do. It saves grief on debugging by highlighting undeclared variables,
objects and other syntactic errors.
Also, you are using
s.LinkFormat.SourceFullName
You do declare what s is, but you do not set it in code, so if the code had
reached that line, you would have gotten a 91 error type, meaning that an
object has not been set.
You would have needed
Set s = something
in your code.
Finally, your With/End With and If/End If blocks were out of whack...
missing a few End or having too many of them... I did not analyse this too
much as I was rewriting the code.
Here is code that I finally tested. (I was writing code "blind" before..)
The SourceFullName does not contain information about worksheets and cell
ranges...
Since you seem to be saying that those do not change, the code can be kept
simple.
Try this:
'_______________________________________
Option Explicit
'_______________________________________
Sub ChangeSource()
Dim k As Long
'Create a variable to store the object reference string.
Dim strSource1 As String
Dim strSource2 As String
Dim strLink1 As String
Dim strLink2 As String
'Edit this to reflect the paths you want to change
'Include just the portion of the path you want to change
'For example, to change links to reflect that files have moved from
'\\boss\P-drive\temp\*.* to
'\\boss\Q-drive\temp\*.*
strSource1 = "P:\Folder1\FolderA\Book1.xls"
strSource2 = "P:Folder2\FolderB\Book2.xls"
strLink1 = "Q:\Folder3\FolderC\Book3.xls"
strLink2 = "Q:\Folder4\FolderD\Book4.xls"
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 = msoLinkedOLEObject Then
' Change Source1 to Link1
With .LinkFormat
If .SourceFullName = strSource1 Then
' Verify that file exists
If Len(Dir$(Replace(.SourceFullName, _
strSource1, strLink1))) > 0 Then
.SourceFullName = strLink1
.Update
End If
ElseIf .SourceFullName = strSource2 Then
' Change Source2 to Link2
' Verify that file exists
If Len(Dir$(Replace(.SourceFullName, _
strSource2, strLink2))) > 0 Then
.SourceFullName = strLink2
.Update
End If
End If
End With
End If
End With
Next k
End With
End Sub
'_______________________________________
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site:
http://www.word.mvps.org
