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
615 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
ID: 24380073
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
ID: 24391517
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
ID: 24393899
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:

929 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

19 Experts available now in Live!

Get 1:1 Help Now