Oli
Sat Aug 04 21:08:00 CDT 2007
Thank you once again for your response, Jean-Guy!! I was so excited that
your latest code might have been the one which would change all my Excel
linksâ?¦ However, when I ran the macro, absolutely nothing happened. Because
I am so VBA illiterate, I canâ??t figure out why. Thereâ??s something not
working quite right in my computer. I donâ??t think the code sees the objects
in my Word document (not in my computer anyway). Therefore, I donâ??t get any
kind of an error message. Nothing changes, because the change is supposed to
happen if there are OLEâ??s in the document. I am wondering if there is a
checkmark that I need to activate in Visual Basic to make it see my embedded
objects. In Visual Basic Editor, under Tools\References, I checked â??MS Excel
11.0 Object Libraryâ?? which was previously unchecked. But, it didnâ??t change
anything.
The example below is supposed to open (for editing) the first embedded OLE
object (defined as a shape) on the active document. (Source:
http://msdn2.microsoft.com/en us/library/Bb214380.aspx) When I run it,
nothing happens:
-----------
Sub OpenFirstSource ()
Dim shapesAll As Shapes
Set shapesAll = ActiveDocument.Shapes
If shapesAll.Count >= 1 Then
If shapesAll(1).Type = mso EmbeddedOLEObject Then
shapesAll(1).OLEFormat.Edit
End If
End If
End Sub
-----------
Once again, here is how I put the Excel objects in my document: I inserted
all the objects by â??Insert\Object\Microsoft Excel Worksheet.â?? I copied the
reference cells in the Excel workbooks, and then I returned back to the
embedded Excel objects in Word document and Pasted Special (Paste Link). I
believe this type of object is called an OLE object, correct? If so, why can
I not make the VB see them? Itâ??s quite frustrating. I hate to make you work
on this code forever, but if you have any ideas or suggestions, I would
really appreciate it. THANK YOU!!
"Jean-Guy Marcil" wrote:
> 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
> jmarcilREMOVE@CAPSsympatico.caTHISTOO
> Word MVP site:
http://www.word.mvps.org
>
>
>