Solved

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

Posted on 2009-05-10
4
613 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:tindavid
  • 2
4 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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
 

Author Comment

by:tindavid
Comment Utility
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now