How to change the Source File for a linked Excel Cell in a Word document by Macro and VB

Here is the problem:

I have a Word Document that has an object (Excel chart) links to Chart in another Excel document. So there is this property of Links in Word document that contains Souce File, Item in File, Link Type. By changing the value of these property, different chart can be linked from Word document.

VB macro required:
1) Source File property contains the absolute path of the linked Excel file name, I will like to change the path using VB macro

2) Changing the value of "Item in File" and Link type is also needed.


T8-Weekly-Report--thisweek-.doc
tindavidAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GrahamSkanConnect With a Mentor RetiredCommented:
This code uses early binding, so you need to set a reference to the Microsoft Word Object library in the Excel VBA editor.

It calls a modified version of the previous macro, then puts two blank lines (paragraphs) after each occurrence the word 'MyKeyword'
Sub EditWordDoc()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("C:\MyFolder\MyFile.Doc")
    ModFields wrdDoc
    With wrdDoc.Range.Text
        .Find = "MyKeyword"
        .Replacement.Text = "MyKeyword" & vbCr & vbCr
        .Execute Replace:=wdReplaceAll
    End With
End Sub
 
Sub ModFields(Doc As Word.Document)
Dim fld As Word.Field
For Each fld In Doc.Fields
    If fld.Type = wdFieldLink Then
        fld.Code = Replace(fld.Code, "\\\\hitfs03", "\\\\NewServer")
    End If
Next fld
End Sub

Open in new window

0
 
GrahamSkanRetiredCommented:
You might need something like this:
Sub ModFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
    If fld.Type = wdFieldLink Then
        fld.Code = Replace(fld.Code, "\\\\hitfs03", "\\\\NewServer")
    End If
Next fld
End Sub

Open in new window

0
 
tindavidAuthor Commented:
Hi Graham;

I want to execute this type of macro from Excel, that is using macro to open an word document and :

1) change the links within the word document
2)  also how can I insert few blank/dummy lines when I find a key word in the word document.

many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.